Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D81B89FB97C; Mon, 6 Aug 2007 12:32:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 10067-04-6; Mon, 6 Aug 2007 12:32:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B49FE9FB899; Mon, 6 Aug 2007 12:32:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l76FVp4o027853; Mon, 6 Aug 2007 11:31:51 -0400 (EDT) To: Henrik Zagerholm cc: Gregory Stark , pgsql-performance@postgresql.org, pgsql-general@postgresql.org Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations. In-reply-to: References: <0C963119-103A-4EB1-B9D8-0FD3E16A11F0@mac.se> <87abt4n75m.fsf@oxford.xeocode.com> Comments: In-reply-to Henrik Zagerholm message dated "Mon, 06 Aug 2007 16:46:43 +0200" Date: Mon, 06 Aug 2007 11:31:51 -0400 Message-ID: <27852.1186414311@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/288 X-Sequence-Number: 118137 Henrik Zagerholm 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