pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: John Arbash Meinel <john@arbash-meinel.com>
Cc: Silke Trissl <trissl@informatik.hu-berlin.de>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tell postgres which index to use?
Date: 09 Feb 2005 15:50:06 -0500
Message-ID: <873bw5e9vl.fsf@stark.xeocode.com> (raw)
In-Reply-To: <420A5836.4080605@arbash-meinel.com>
References: <420A3309.5040507@informatik.hu-berlin.de>
	<200502090941.01447.josh@agliodbs.com>
	<420A4F2A.1060506@informatik.hu-berlin.de>
	<420A5836.4080605@arbash-meinel.com>

John Arbash Meinel <john@arbash-meinel.com> writes:

> >    ->  Hash  (cost=1418.68..1418.68 rows=3226 width=4) (actual
> > time=77.062..77.062 rows=0 loops=1)
> 
> This seems to be at least one of the problems. The planner thinks there
> are going to be 3000+ rows, but in reality there are 0.

No, that's a red herring. Hash nodes always report 0 rows. 

> >  Nested Loop  (cost=0.00..23849.81 rows=7533 width=8) (actual time=0.341..198.162 rows=5798 loops=1)
> >    ->  Seq Scan on pdb_entry  (cost=0.00..1418.68 rows=3226 width=4) (actual time=0.145..78.177 rows=3329 loops=1)
> >          Filter: ((resolution > 0::double precision) AND (resolution < 1.7::double precision))
> >    ->  Index Scan using chain_pdb_id_ind on "chain"  (cost=0.00..6.87 rows=6 width=8) (actual time=0.021..0.027 rows=2 loops=3329)
> >          Index Cond: ("outer".id = "chain".pdb_id)

The actual number of records is pretty close to the estimated number. And the
difference seems to come primarily from selectivity of the join where it
thinks an average of 6 rows will match every row whereas in fact an average of
about 2 rows matches.

So it thinks it's going to read about 18,000 records out of 67,000 or about
25%. In that case the sequential scan is almost certainly better. In fact it's
going to read about 6,000 or just under 10%, in which case the sequential scan
is probably still better but it's not so clear.

I suspect the only reason you're seeing such a big difference when I would
expect it to be about even is because nearly all the data is cached. In that
case the non-sequential access pattern of the nested loop has little effect.

You might get away with lowering random_page_cost but since it thinks it's
going to read 25% of the table I suspect you'll have to get very close to 1
before it switches over, if it does even then. Be careful about tuning
settings like this based on a single query, especially to unrealistically low
values.

You might also want to try raising the statistics target on pdb_entry. See if
that makes the estimate go down from 6 to closer to 2.

-- 
greg




view thread (5+ messages)

Message-ID: <873bw5e9vl.fsf@stark.xeocode.com>
Permalink:  ../873bw5e9vl.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/873bw5e9vl.fsf@stark.xeocode.com

 · 

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: gsstark@mit.edu, john@arbash-meinel.com, trissl@informatik.hu-berlin.de, josh@agliodbs.com
  Subject: Re: Tell postgres which index to use?
  In-Reply-To: <873bw5e9vl.fsf@stark.xeocode.com>

* 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