X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EE54AD1BCA7 for ; Tue, 27 Apr 2004 19:00:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49648-02 for ; Tue, 27 Apr 2004 19:00:08 -0300 (ADT) Received: from obie.mrkdns.com (obie.mrkdns.com [69.93.72.82]) by svr1.postgresql.org (Postfix) with ESMTP id 278A4D1BC54 for ; Tue, 27 Apr 2004 19:00:05 -0300 (ADT) Received: from 82-33-1-22.cable.ubr13.newt.blueyonder.co.uk ([82.33.1.22] helo=chuckie.co.uk) by obie.mrkdns.com with asmtp (Exim 4.30) id 1BIacd-0002my-H7; Tue, 27 Apr 2004 23:00:11 +0100 Message-ID: <408ED7FC.2010705@chuckie.co.uk> Date: Tue, 27 Apr 2004 23:00:28 +0100 From: Nick Barr User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Vitaly Belman Cc: pgsql-performance@postgresql.org Subject: Re: Simply join in PostrgeSQL takes too long References: <282909734.20040428002740@012.net.il> In-Reply-To: <282909734.20040428002740@012.net.il> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - obie.mrkdns.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - chuckie.co.uk X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/468 X-Sequence-Number: 6768 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