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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers-win32 pgsql-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

Browse pgsql-hackers-win32 by date

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

Browse pgsql-performance by date

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