pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nick Barr <nicky@chuckie.co.uk>
To: Vitaly Belman <vitalib@012.net.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simply join in PostrgeSQL takes too long
Date: Tue, 27 Apr 2004 23:00:28 +0100
Message-ID: <408ED7FC.2010705@chuckie.co.uk> (raw)
In-Reply-To: <282909734.20040428002740@012.net.il>
References: <282909734.20040428002740@012.net.il>

Vitaly Belman wrote:
> Hello pgsql-performance,
> 
>   I discussed the whole subject for some time in DevShed and didn't
>   achieve much (as for results). I wonder if any of you guys can help
>   out:
> 
>   http://forums.devshed.com/t136202/s.html
> 

So cutting and pasting:

----- SCHEMA -----
CREATE TABLE bv_bookgenres (
     book_id INT NOT NULL,
     genre_id INT NOT NULL
);
CREATE TABLE bv_genre_children (
     genre_id INT,
     genre_child_id INT
);
-------------------

----- QUERY -----
select DISTINCT
       book_id
from
   bookgenres,
   genre_children
WHERE
      bookgenres.genre_id = genre_children.genre_child_id AND
      genre_children.genre_id = 1
LIMIT 10
-----------------

----- EXPLAIN ANALYZE -----
QUERY PLAN
Limit  (cost=6503.51..6503.70 rows=10 width=4) (actual 
time=703.000..703.000 rows=10 loops=1)
   ->  Unique  (cost=6503.51..6738.20 rows=12210 width=4) (actual 
time=703.000..703.000 rows=10 loops=1)
         ->  Sort  (cost=6503.51..6620.85 rows=46937 width=4) (actual 
time=703.000..703.000 rows=24 loops=1)
               Sort Key: bv_bookgenres.book_id
               ->  Merge Join  (cost=582.45..2861.57 rows=46937 width=4) 
(actual time=46.000..501.000 rows=45082 loops=1)
                     Merge Cond: ("outer".genre_id = "inner".genre_child_id)
                     ->  Index Scan using genre_id on bv_bookgenres 
(cost=0.00..1462.84 rows=45082 width=8) (actual time=0.000..158.000 
rows=45082 loops=1)
                     ->  Sort  (cost=582.45..598.09 rows=6256 width=2) 
(actual time=46.000..77.000 rows=49815 loops=1)
                           Sort Key: bv_genre_children.genre_child_id
                           ->  Index Scan using genre_id2 on 
bv_genre_children  (cost=0.00..187.98 rows=6256 width=2) (actual 
time=0.000..31.000 rows=6379 loops=1)
                                 Index Cond: (genre_id = 1)
Total runtime: 703.000 ms
-------------------------------

----- CONF SETTINGS -----
shared_buffers = 1000		# min 16, at least max_connections*2, 8KB each
sort_mem = 10000
#work_mem = 1024		# min 64, size in KB
#maintenance_work_mem = 16384	# min 1024, size in KB
#max_stack_depth = 2048		# min 100, size in KB
-------------------------

Have you VACUUM ANALYZED recently. If not do that then rerun the EXPLAIN 
ANALYZE.

You might wanna bump shared_buffers. You have 512MB RAM right? You 
probably want to bump shared_buffers to 10000, restart PG then run a 
VACUUM ANALYZE. Then rerun the EXPLAIN ANALYZE.

If that doesnt help try doing a

ALTER TABLE bv_genre_children ALTER COLUMN genre_child_id SET STATISTICS 
100;

followed by a:

VACUUM ANALYZE bv_genre_children;

You might also want to be tweaking the effective_cache_size parameter in 
  postgresql.conf, but I am unsure how this would work on Windows. Does 
Windows have a kernel disk cache anyone?




HTH

Nick








view thread (12+ messages)  latest in thread

Message-ID: <408ED7FC.2010705@chuckie.co.uk>
Permalink:  ../408ED7FC.2010705@chuckie.co.uk/
Also on:    postgresql.org/message-id/408ED7FC.2010705@chuckie.co.uk

 · 

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: nicky@chuckie.co.uk, vitalib@012.net.il
  Subject: Re: Simply join in PostrgeSQL takes too long
  In-Reply-To: <408ED7FC.2010705@chuckie.co.uk>

* 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