Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations.

6 aug 2007 kl. 17:31 skrev Tom Lane:

> Henrik Zagerholm <henke@mac.se> writes:
>>                         WHERE file_indexed IS FALSE
>>                                                  AND file_copied
>> IS TRUE
>>                                                  AND file_size <
>> (1024)
>>                                                  AND LOWER
>> (file_suffix) IN(
>>                                                          SELECT LOWER
>> (filetype_suffix) FROM tbl_filetype_suffix WHERE
>> filetype_suffix_index IS TRUE
>>                                                  ) AND fk_archive_id
>> = 115 ORDER BY fk_tar_id
>
> Do you really need the lower() calls there?  The planner is getting
> the
> wrong estimate for the selectivity of the IN-clause, which is likely
> because it has no statistics about lower(file_suffix) or
> lower(filetype_suffix).
>
> If you don't want to constrain the data to be already lower'd, then
> setting up functional indexes on the two lower() expressions should
> prompt ANALYZE to track stats for them.
>

OK, thanx for the tip. I actually think that all the suffixes are
lower case so the lower should go.
But would this really impact the sequential scan on tbl_file_structure?

->Seq Scan on tbl_file_structure  (cost=0.00..167417.09 rows=7902309
width=16) (actual time=9.581..33702.852 rows=7801334 loops=1)

At what point does the planner choose seq scans? I've seen the
planner use seq scan even though only 1% of the joining tables rows
are selected.
If the filter gives me 70k rows from tbl_file and tbl_file_structure
has 8 million rows why does the planner choose seq scans?

Cheers,
Henrik



>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate