Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations. - Mailing list pgsql-general

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.

            regards, tom lane