X-Original-To: pgsql-hackers-win32-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EB5F132A141; Wed, 29 Sep 2004 17:18:42 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04766-10; Wed, 29 Sep 2004 16:18:37 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 886E132A135; Wed, 29 Sep 2004 17:18:37 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8TGID9W029955; Wed, 29 Sep 2004 12:18:13 -0400 (EDT) To: John Meinel Cc: pgsql-hackers-win32 , pgsql-performance@postgresql.org Subject: Re: Poor Performance for large queries in functions In-reply-to: <415A575F.2060802@johnmeinel.com> References: <4155C955.40808@dunslane.net> <415A288F.2030002@johnmeinel.com> <22682.1096434647@sss.pgh.pa.us> <415A575F.2060802@johnmeinel.com> Comments: In-reply-to John Meinel message dated "Wed, 29 Sep 2004 01:34:07 -0500" Date: Wed, 29 Sep 2004 12:18:13 -0400 Message-ID: <29954.1096474693@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/160 X-Sequence-Number: 2290 [ enlarging on Richard's response a bit ] John Meinel writes: > jfmeinel=> explain analyze execute myget(30000); > QUERY PLAN > -------------------------------------------------------------------- > Seq Scan on tdata (cost=0.00..9773.10 rows=181923 width=4) > (actual time=1047.000..1047.000 rows=0 loops=1) > Filter: (project_id = $1) > Total runtime: 1047.000 ms > jfmeinel=> explain analyze select id from tdata where project_id = 30000; > QUERY PLAN > ------------------------------------------------------------------------- > Index Scan using tdata_project_id_idx on tdata (cost=0.00..4.20 > rows=1 width=4) (actual time=0.000..0.000 rows=0 loops =1) > Index Cond: (project_id = 30000) > Total runtime: 0.000 ms > So notice that when doing the actual select it is able to do the index > query. But for some reason with a prepared statement, it is not able to > do it. This isn't a "can't do it" situation, it's a "doesn't want to do it" situation, and it's got nothing whatever to do with null or not null. The issue is the estimated row count, which in the first case is so high as to make the seqscan approach look cheaper. So the real question here is what are the statistics on the column that are making the planner guess such a large number when it has no specific information about the compared-to value. Do you have one extremely common value in the column? Have you done an ANALYZE recently on the table, and if so can you show us the pg_stats row for the column? regards, tom lane