From: Tom Lane <tgl@sss.pgh.pa.us>
To: Henrik Zagerholm <henke@mac.se>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations.
Date: Mon, 06 Aug 2007 11:31:51 -0400
Message-ID: <27852.1186414311@sss.pgh.pa.us> (raw)
In-Reply-To: <A9EBAF31-5AEF-4F1C-B845-2D81D9936560@mac.se>
References: <0C963119-103A-4EB1-B9D8-0FD3E16A11F0@mac.se>
<87abt4n75m.fsf@oxford.xeocode.com>
<A9EBAF31-5AEF-4F1C-B845-2D81D9936560@mac.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, henke@mac.se, stark@enterprisedb.com, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations.
In-Reply-To: <27852.1186414311@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox