X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F1C7B32A126 for ; Wed, 29 Sep 2004 18:09:57 +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 23726-07 for ; Wed, 29 Sep 2004 17:09:45 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 54BE332A20A for ; Wed, 29 Sep 2004 18:09:37 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.12.9/8.12.9) with ESMTP id i8TH94vK001902; sent by ; Wed, 29 Sep 2004 12:09:33 -0500 (CDT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by server11.icaen.uiowa.edu (8.12.9/smtp-service-1.6) with ESMTP id i8TGiE4J023878; (envelope-from ) Wed, 29 Sep 2004 11:44:45 -0500 (CDT) Message-ID: <415AE65E.3030200@johnmeinel.com> Date: Wed, 29 Sep 2004 11:44:14 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: [pgsql-hackers-win32] Poor Performance for large queries References: <4155C955.40808@dunslane.net> <415A288F.2030002@johnmeinel.com> <22682.1096434647@sss.pgh.pa.us> <415A575F.2060802@johnmeinel.com> <29954.1096474693@sss.pgh.pa.us> In-Reply-To: <29954.1096474693@sss.pgh.pa.us> X-Enigmail-Version: 0.86.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigCDEA57F1EE45456906C2BF68" X-Virus-Scanned: clamd / ClamAV version 0.75, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean 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/328 X-Sequence-Number: 8456 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigCDEA57F1EE45456906C2BF68 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > [ 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 > The answer is "yes" that particular column has very common numbers in it. Project id is a number from 1->21. I ended up modifying my query such that I do the bulk of the work in a regular UNION SELECT so that all that can be optimized, and then I later do another query for this row in an 'EXECUTE ...' so that unless I'm actually requesting a small number, the query planner can notice that it can do an indexed query. I'm pretty sure this is just avoiding worst case scenario. Because it is true that if I use the number 18, it will return 500,000 rows. Getting those with an indexed lookup would be very bad. But typically, I'm doing numbers in a very different range, and so the planner was able to know that it would not likely find that number. Thanks for pointing out what the query planner was thinking, I was able to work around it. John =:-> --------------enigCDEA57F1EE45456906C2BF68 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBWuZeJdeBCYSNAAMRAnTJAKCj6O4RFEhlaH47pwjbdSx5TRiu6ACgnv2s I5W1E4PfmcCViXjnpiMFfFQ= =yMm2 -----END PGP SIGNATURE----- --------------enigCDEA57F1EE45456906C2BF68--