pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Meinel <john@johnmeinel.com>
Cc: pgsql-hackers-win32 <pgsql-hackers-win32@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor Performance for large queries in functions
Date: Wed, 29 Sep 2004 12:18:13 -0400
Message-ID: <29954.1096474693@sss.pgh.pa.us> (raw)
In-Reply-To: <415A575F.2060802@johnmeinel.com>
References: <4155C955.40808@dunslane.net>
	<415A288F.2030002@johnmeinel.com>
	<22682.1096434647@sss.pgh.pa.us>
	<415A575F.2060802@johnmeinel.com>

[ 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



view thread (4+ messages)

Message-ID: <29954.1096474693@sss.pgh.pa.us>
Permalink:  ../29954.1096474693@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29954.1096474693@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, john@johnmeinel.com, pgsql-hackers-win32@postgresql.org
  Subject: Re: Poor Performance for large queries in functions
  In-Reply-To: <29954.1096474693@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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