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

* Re: [PERFORM] Poor Performance for large queries
@ 2004-09-29 08:40 ` Richard Huxton <dev@archonet.com>
  2004-09-29 14:56   ` Re: [PERFORM] Poor Performance for large queries John Meinel <john@johnmeinel.com>
  1 sibling, 1 reply; 3+ messages in thread

From: Richard Huxton @ 2004-09-29 08:40 UTC (permalink / raw)
  To: John Meinel <john@johnmeinel.com>; +Cc: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>; pgsql-performance

John Meinel wrote:
> 
> 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?

In the index-using example, PG knows the value you are comparing to. So, 
it can make a better estimate of how many rows will be returned. With 
the prepared/compiled version it has to come up with a plan that makes 
sense for any value.

If you look back at the explain output you'll see PG is guessing 181,923 
rows will match with the prepared query but only 1 for the second query. 
If in fact you returned that many rows, you wouldn't want to use the 
index - it would mean fetching values twice.

The only work-around if you are using plpgsql functions is to use 
EXECUTE to make sure your queries are planned for each value provided.

-- 
   Richard Huxton
   Archonet Ltd



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

* Re: [PERFORM] Poor Performance for large queries
  2004-09-29 08:40 ` Re: [PERFORM] Poor Performance for large queries Richard Huxton <dev@archonet.com>
@ 2004-09-29 14:56   ` John Meinel <john@johnmeinel.com>
  0 siblings, 0 replies; 3+ messages in thread

From: John Meinel @ 2004-09-29 14:56 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>; pgsql-performance

Richard Huxton wrote:
> John Meinel wrote:
> 
>>
>> 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?
> 
> 
> In the index-using example, PG knows the value you are comparing to. So, 
> it can make a better estimate of how many rows will be returned. With 
> the prepared/compiled version it has to come up with a plan that makes 
> sense for any value.
> 
> If you look back at the explain output you'll see PG is guessing 181,923 
> rows will match with the prepared query but only 1 for the second query. 
> If in fact you returned that many rows, you wouldn't want to use the 
> index - it would mean fetching values twice.
> 
> The only work-around if you are using plpgsql functions is to use 
> EXECUTE to make sure your queries are planned for each value provided.
> 
I suppose that make sense. If the number was small (< 100) then there 
probably would be a lot of responses. Because the tproject table is all 
small integers.

But for a large number, it probably doesn't exist on that table at all.

Thanks for the heads up.

John
=:->

Attachments:

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

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

* Re: [pgsql-hackers-win32] Poor Performance for large queries
@ 2004-09-29 16:44 ` John Meinel <john@johnmeinel.com>
  1 sibling, 0 replies; 3+ messages in thread

From: John Meinel @ 2004-09-29 16:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom Lane wrote:
> [ 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
> 

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
=:->

Attachments:

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

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


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

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-29 08:40 ` Richard Huxton <dev@archonet.com>
2004-09-29 14:56   ` John Meinel <john@johnmeinel.com>
2004-09-29 16:44 ` John Meinel <john@johnmeinel.com>

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