Skip site navigation (1) Skip section navigation (2)

Re: Poor Performance for large queries in functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Meinel <john(at)johnmeinel(dot)com>
Cc: pgsql-hackers-win32 <pgsql-hackers-win32(at)postgresql(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor Performance for large queries in functions
Date: 2004-09-29 16:18:13
Message-ID: 29954.1096474693@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers-win32pgsql-performance
[ enlarging on Richard's response a bit ]

John Meinel <john(at)johnmeinel(dot)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

In response to

Responses

pgsql-performance by date

Next:From: John MeinelDate: 2004-09-29 16:44:14
Subject: Re: [pgsql-hackers-win32] Poor Performance for large queries
Previous:From: Dirk Lutzeb├ĄckDate: 2004-09-29 15:55:45
Subject: why does explain analyze differ so much from estimated explain?

pgsql-hackers-win32 by date

Next:From: John MeinelDate: 2004-09-29 16:44:14
Subject: Re: [pgsql-hackers-win32] Poor Performance for large queries
Previous:From: John MeinelDate: 2004-09-29 14:56:27
Subject: Re: [PERFORM] Poor Performance for large queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group