pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John A Meinel <john@arbash-meinel.com>
To: Richard Rowell <richard@bowmansystems.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improve performance of query
Date: Thu, 16 Dec 2004 10:59:05 -0600
Message-ID: <41C1BED9.2050206@arbash-meinel.com> (raw)
In-Reply-To: <1103213467.9908.65.camel@richard>
References: <1103213467.9908.65.camel@richard>

The first thing to check... Did you do a recent VACUUM ANALYZE? This 
updates all the statistics. There are a number of places where it says 
"rows=1000" which is usually the "I have no idea, let me guess 1000". 
Also, there are a number of places where the estimates are pretty far 
off. For instance:

Richard Rowell wrote:

>->  Subquery Scan "*SELECT* 1"  (cost=0.00..64034.15 rows=10540 width=24) (actual time=279.089..4419.371 rows=161 loops=1)
>  
>
estimating 10,000 when only 161 is a little bit different.

>                                 ->  Seq Scan on da_answer a  (cost=0.00..63928.75 rows=10540 width=24) (actual time=279.080..4418.808 rows=161 loops=1)
>                                       Filter: ((date_effective <= 9999999999::double precision) AND (inactive <> 1) AND (subplan))
>  
>
Though this could be a lack of cross-column statistics. If 2 columns are 
correlated, the planner isn't as accurate as it could be. Also, 
date_effective <= 9999999999 doesn't seem very restrictive, could you 
use a between statement? (date between 0 and 9999999). I know for 
timestamps usually giving a between is better than a single sided query.

This one was underestimated.

>->  Subquery Scan "*SELECT* 2"  (cost=988627.58..989175.52 rows=2799 width=24) (actual time=290.730..417.720 rows=7556 loops=1)
>                                 ->  Hash Join  (cost=988627.58..989147.53 rows=2799 width=24) (actual time=290.722..395.739 rows=7556 loops=1)
>                                       Hash Cond: ("outer".main_id = "inner".uid)
>  
>
This is one of the ones that looks like it didn't have any ideas. It 
could be because of the function. You might consider adding a function 
index, though I think there are some caveats there.

>->  Function Scan on svp_getparentproviderids  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.473..0.474 rows=1 loops=1)
>  
>
Another very poor estimation. It might be a need to increase the 
statistics for this column (ALTER TABLE, ALTER COLUMN, SET STATISTICS). 
IIRC, compared with other db's postgres defaults to a much lower 
statistics value. Try changing it from 10 (?) to 100 or so. There was a 
discussion that every column with an index should use higher statistics.

>->  Index Scan using in_da_dr_type_provider on da_data_restrict  (cost=0.00..145.50 rows=46 width=8) (actual time=0.041..26.627 rows=7280 loops=1)
>  
>
I'm not a great optimizer, these are just some first things to look at. 
Your sort mem seems pretty low to me (considering you have 1GB of RAM). 
Perhaps you could bump that up to 40MB instead of 4MB. Also, if you run 
this query twice in a row, is it still slow? (Sometimes it takes a bit 
of work to get the right indexes loaded into ram, but then it is faster.)

Just some guesses,
John
=:->

Attachments:

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

view thread (6+ messages)  latest in thread

Message-ID: <41C1BED9.2050206@arbash-meinel.com>
Permalink:  ../41C1BED9.2050206@arbash-meinel.com/
Also on:    postgresql.org/message-id/41C1BED9.2050206@arbash-meinel.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: john@arbash-meinel.com, richard@bowmansystems.com
  Subject: Re: Improve performance of query
  In-Reply-To: <41C1BED9.2050206@arbash-meinel.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