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 A01E632A161; Wed, 29 Sep 2004 08:13:31 +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 13865-04; Wed, 29 Sep 2004 07:13:19 +0000 (GMT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 360CC32A11D; Wed, 29 Sep 2004 08:13:21 +0100 (BST) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by www.postgresql.com (Postfix) with ESMTP id ECAF65A1094; Wed, 29 Sep 2004 08:13:11 +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 i8T77puk018683; sent by ; Wed, 29 Sep 2004 02:08:03 -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 i8T6Y6MZ011499; (envelope-from ) Wed, 29 Sep 2004 01:34:07 -0500 (CDT) Message-ID: <415A575F.2060802@johnmeinel.com> Date: Wed, 29 Sep 2004 01:34:07 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-hackers-win32 , pgsql-performance@postgresql.org Subject: Re: Poor Performance for large queries in functions References: <4155C955.40808@dunslane.net> <415A288F.2030002@johnmeinel.com> <22682.1096434647@sss.pgh.pa.us> In-Reply-To: <22682.1096434647@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="------------enigF2DFFA754DD831AA68A3C06E" 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/157 X-Sequence-Number: 2287 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigF2DFFA754DD831AA68A3C06E Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > John Meinel writes: > >>... However, if I try to >>bundle this query up into a server side function, it runs very slow (10 >>seconds). I'm trying to figure out why, but since I can't run EXPLAIN >>ANALYZE inside a function, I don't really know what else to do. > > > A parameterized query inside a function is basically the same as a > PREPARE'd query with parameters at the SQL level. So you can > investigate what's happening here with > > PREPARE foo(int) AS > SELECT * FROM object WHERE id in ( > SELECT id FROM data_t WHERE project_id = $1 > UNION SELECT ... ; > > EXPLAIN ANALYZE EXECUTE foo(48542); > > I'm not sure where the problem is either, so please do send along the > results. > > regards, tom lane > > PS: pgsql-performance would be a more appropriate venue for this > discussion. Well, I think I tracked the problem down to the fact that the column does not have a "not null" constraint on it. Here is a demonstration. Basically, I have 3 tables, tobjects, tdata, and tproject. tdata basically just links between tobjects and tproject, but isn't required to link to tproject. Yes, the real data has more columns, but this shows the problem. jfmeinel=> \d tobjects Table "public.tobjects" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "tobjects_pkey" primary key, btree (id) jfmeinel=> \d tproject Table "public.tproject" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "tproject_pkey" primary key, btree (id) jfmeinel=> \d tdata Table "public.tdata" Column | Type | Modifiers ------------+---------+----------- id | integer | not null project_id | integer | Indexes: "tdata_pkey" primary key, btree (id) "tdata_project_id_idx" btree (project_id) Foreign-key constraints: "tdata_id_fkey" FOREIGN KEY (id) REFERENCES tobjects(id) ON UPDATE CASCADE ON DELETE CASCADE "tdata_project_id_fkey" FOREIGN KEY (project_id) REFERENCES tproject(id) ON UPDATE CASCADE ON DELETE SET DEFAULT jfmeinel=> select count(*) from tdata; count -------- 545768 jfmeinel=> select count(*) - count(project_id) from tdata; ?column? ---------- 240 So tdata(project_id) is almost completely full, of the 540000+ entries, only 240 are null. jfmeinel=> prepare myget(int) as select id from tdata jfmeinel-> where project_id = $1; PREPARE 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. Any ideas? Since I only have the integers now, I can send the data to someone if they care to investigate it. It comes to 2.2M as a .tar.bz2, so obviously I'm not going to spam the list. If I rewrite myget as: prepare myget(int) as select id from tdata where project_id = 30000; it does the right thing again. So it's something about how a variable interacts with an indexed column with null values. Note: I've tried creating a script that generates dummy data to show this problem and I have failed (it always performed the query correctly.) But this test data definitely shows the problem. And yes, I've vacuum analyzed all over the place. John =:-> PS> I tested this on PostgreSQL 7.4.3, and it did not demonstrate this problem. I am using PostgreSQL 8.0.0beta2 (probably -dev1) --------------enigF2DFFA754DD831AA68A3C06E 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 iD8DBQFBWldjJdeBCYSNAAMRAkE2AJ4ilrPW6KfvE7hCPaSgQnVziIW32QCgxAgC WMiDT4P3CqY0usnlvrYiDhI= =65BZ -----END PGP SIGNATURE----- --------------enigF2DFFA754DD831AA68A3C06E--