pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Poor Performance for large queries in functions
4+ messages / 2 participants
[nested] [flat]

* Poor Performance for large queries in functions
@ 2004-09-29 03:14  John Meinel <john@johnmeinel.com>
  0 siblings, 1 reply; 4+ messages in thread

From: John Meinel @ 2004-09-29 03:14 UTC (permalink / raw)
  To: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>

I've been using postgres 8.0 beta for win32 for quite a while now, and I 
am very happy with it. However, I am having an odd problem. Basically, I 
have a large query which is a bunch of UNION SELECTs from a bunch of 
different tables. I have all the necessary columns indexed, and when I 
run the query by hand, it runs very fast (20ms). 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.

The layout of my database is a bunch of tables that all have an object 
id associated with them. There is a main object table that defines per 
object permissions, and then all of the tables refer to eachother by the 
unique id. What I'm trying to do is get a list of objects that might 
refer to a given id.

Here is the query. 48542 is just one of the object ids. Some of these 
tables have 500,000 rows, but most are quite small, and the result is 
only 3 rows.

SELECT * FROM object WHERE id in (
	SELECT id FROM data_t WHERE project_id = 48542
	UNION SELECT id FROM analyzeset_t
		WHERE subject_id = 48542
		   OR img_id = 48542
		   OR hdr_id = 48542
	UNION SELECT id FROM bdi_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM crq_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM dcmfile_t WHERE dcmseries_id = 48542
	UNION SELECT id FROM dcmseries_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM dcmstudy_t
		WHERE dcmsub_id = 48542
		   OR consent_id = 48542
	UNION SELECT id FROM hsq_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM job_t WHERE claimed_id = 48542
	UNION SELECT id FROM loc_t WHERE contact_id = 48542
	UNION SELECT id FROM pathslide_t WHERE plane_id = 48542
	UNION SELECT id FROM pft_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM pftblood_t WHERE pft_id = 48542
	UNION SELECT id FROM pftdata_t WHERE pft_id = 48542
	UNION SELECT id FROM pftpred_t WHERE pft_id = 48542
	UNION SELECT id FROM run_t WHERE job_id = 48542
	UNION SELECT id FROM scanread_t
		WHERE readby_id = 48542
		   OR dcmstudy_id = 48542
	UNION SELECT id FROM service_t WHERE comp_id = 48542
	UNION SELECT id FROM sliceplane_t WHERE tissue_id = 48542
	UNION SELECT id FROM store_t WHERE loc_id = 48542
	UNION SELECT id FROM subject_t WHERE supersub_id = 48542
	UNION SELECT id FROM vc_t WHERE dcmstudy_id = 48542
	UNION SELECT id FROM vcdata_t WHERE vc_id = 48542
	UNION SELECT id FROM vcdyn_t WHERE vc_id = 48542
	UNION SELECT id FROM vcstatic_t WHERE vc_id = 48542
	UNION SELECT child_id as id FROM datapar_t WHERE par_id = 48542
	UNION SELECT par_id as id FROM datapar_t WHERE child_id = 48542
	UNION SELECT store_id as id FROM finst_t WHERE file_id = 48542
	UNION SELECT file_id as id FROM finst_t WHERE store_id = 48542
	UNION SELECT from_id as id FROM link_t WHERE to_id = 48542
	UNION SELECT to_id as id FROM link_t WHERE from_id = 48542
	UNION SELECT data_id as id FROM proc_t WHERE filter_id = 48542
	UNION SELECT filter_id as id FROM proc_t WHERE data_id = 48542
	UNION SELECT subject_id as id FROM subdata_t WHERE data_id = 48542
	UNION SELECT data_id as id FROM subdata_t WHERE subject_id = 48542
)
;

If I run this exact query, it takes 21 ms.

I tried to wrap it into a function with:

create function getrefs(int) returns setof object as '
...
' language sql;

Where the ... is just the same query with 48542 replaced with $1.
select getrefs(48542);
takes 10356.000ms

I have also tried some other things such as:

CREATE OR REPLACE FUNCTION mf_getrefobjs(int) RETURNS boolean AS '
DECLARE
	oldid alias for $1;
BEGIN
	DROP TABLE refobjs;
     CREATE TEMPORARY TABLE refobjs AS
     SELECT * FROM object WHERE id in (
         SELECT id FROM data_t WHERE project_id = oldid
...
     );
	RETURN 1;
end;
' LANGUAGE plpgSQL;

I have tried returning cursors (they return fast, but the first FETCH 
NEXT, is very slow.)

Does anyone know why this would be? Is it a problem that in a function 
it doesn't notice that all of the '=' are the same number, and it cannot 
optimize the query? Is there some way I could force an EXPLAIN ANALYZE? 
(if i run it on SELECT getrefs() I just get that it made 1 function call.)

I've tried adding oldid::int in case it was a casting problem.

Actually, I've also tried stripping it all the way down to one query:
create or replace function getrefs(int4) returns setof object as '
     SELECT * FROM object WHERE id in (
         SELECT id FROM data_t WHERE project_id = $1::int
     );
' language sql;

And that takes 3ms to return 0 rows. It actually seems like it is 
ignoring the index on project_id in this case.

It is true that project_id can be Null. It seems that if I make this 
query on other tables that have "not null" the don't have the same 
performance hit.

Any help would be much appreciated, even if it is just giving me a 
better place to ask.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Poor Performance for large queries in functions
@ 2004-09-29 05:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: John Meinel <john@johnmeinel.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2004-09-29 05:10 UTC (permalink / raw)
  To: John Meinel <john@johnmeinel.com>; +Cc: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>

John Meinel <john@johnmeinel.com> 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.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Poor Performance for large queries in functions
@ 2004-09-29 06:34  John Meinel <john@johnmeinel.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: John Meinel @ 2004-09-29 06:34 UTC (permalink / raw)
  To: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>; pgsql-performance

Tom Lane wrote:
> John Meinel <john@johnmeinel.com> 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)

Attachments:

  signature.asc (application/pgp-signature, 252B) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Poor Performance for large queries in functions
@ 2004-09-29 16:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: John Meinel <john@johnmeinel.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2004-09-29 16:18 UTC (permalink / raw)
  To: John Meinel <john@johnmeinel.com>; +Cc: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>; pgsql-performance

[ enlarging on Richard's response a bit ]

John Meinel <john@johnmeinel.com> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-09-29 16:18 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-29 03:14 Poor Performance for large queries in functions John Meinel <john@johnmeinel.com>
2004-09-29 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-29 06:34   ` John Meinel <john@johnmeinel.com>
2004-09-29 16:18     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox