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 D881E3A4087 for ; Mon, 8 Nov 2004 19:30:58 +0000 (GMT) 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 95729-10 for ; Mon, 8 Nov 2004 19:30:55 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 0B4623A406A for ; Mon, 8 Nov 2004 19:30:54 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id iA8JTn330779; Mon, 8 Nov 2004 13:29:49 -0600 Message-ID: <418FC923.2090704@johnmeinel.com> Date: Mon, 08 Nov 2004 13:29:39 -0600 From: John Meinel User-Agent: Mozilla Thunderbird 0.7.1 (X11/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: patrick ~ Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: vacuum analyze slows sql query References: <20041108185702.99883.qmail@web52103.mail.yahoo.com> In-Reply-To: <20041108185702.99883.qmail@web52103.mail.yahoo.com> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigB574F12AD6861FD6080C3F2E" 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: 200411/146 X-Sequence-Number: 9100 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigB574F12AD6861FD6080C3F2E Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit patrick ~ wrote: [...] > pkk=# explain analyze execute pkk_01( 241 ); > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Result (cost=2.66..2.67 rows=1 width=0) (actual time=2872.211..2872.213 > rows=1 loops=1) > InitPlan > -> Limit (cost=0.00..2.66 rows=1 width=4) (actual > time=2872.189..2872.189 rows=0 loops=1) > -> Seq Scan on pkk_purchase p0 (cost=0.00..37225.83 rows=13983 > width=4) (actual time=2872.180..2872.180 rows=0 loops=1) > Filter: ((offer_id = $1) AND (((expire_time)::timestamp with > time zone > now()) OR (expire_time IS NULL) OR (pending = true)) AND > ((cancel_date IS NULL) OR (pending = true))) > Total runtime: 2872.339 ms > (6 rows) > > Time: 2873.479 ms > [...] > So, is this the ultimate solution to this issue? > > --patrick It's not so much that correlation is < 0.5. It sounds like you're running into the same issue that I ran into in the past. You have a column with lots of repeated values, and a few exceptional ones. Notice this part of the query: -> Seq Scan on pkk_purchase p0 (cost rows=13983) (actual rows=0) For a general number, it thinks it might return 14,000 rows, hence the sequential scan. Before you do ANALYZE, it uses whatever defaults exist, which are probably closer to reality. The problem is that you probably have some values for pkk_purchase where it could return 14,000 rows (possibly much much more). And for those, seq scan is the best plan. However, for the particular value that you are testing, there are very few (no) entries in the table. With a prepared statement (or a function) it has to determine ahead of time what the best query is without knowing what value you are going to ask for. Lets say for a second that you manage to trick it into using index scan, and then you actually call the function with one of the values that returns 1,000s of rows. Probably it will take 10-100 times longer than if it used a seq scan. So what is the solution? The only one I'm aware of is to turn your static function into a dynamic one. So somewhere within the function you build up a SQL query string and call EXECUTE str. This forces the query planner to be run every time you call the function. This means that if you call it will a "nice" value, you will get the fast index scan, and if you call it with a "bad" value, it will switch back to seq scan. The downside is you don't get much of a benefit from using as stored procedure, as it has to run the query planner all the time (as though you issue the query manually each time.) But it still might be better for you in the long run. Example: instead of create function test(int) returns int as ' declare x alias for $1; int y; begin select into y ... from ... where id=x limit ...; return y; end '; use this format create function test(int) returns int as ' declare x alias for $1; int y; begin EXECUTE ''select into y ... from ... where id='' ||quote_literal(x) || '' limit ...''; return y; end; '; I think that will point you in the right direction. John =:-> --------------enigB574F12AD6861FD6080C3F2E 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 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBj8kjJdeBCYSNAAMRArHjAJ9s1e83jJcer3rIGlLYMPIYuZe3wgCgiuXH OGHybM4byIqBPHLwlDmWY5s= =Yw4f -----END PGP SIGNATURE----- --------------enigB574F12AD6861FD6080C3F2E--