X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EBE003A5323 for ; Sun, 9 Jan 2005 04:44:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92201-04 for ; Sun, 9 Jan 2005 04:44:47 +0000 (GMT) Received: from mail01.syd.optusnet.com.au (mail01.syd.optusnet.com.au [211.29.132.182]) by svr1.postgresql.org (Postfix) with ESMTP id 3AA973A5250 for ; Sun, 9 Jan 2005 04:44:46 +0000 (GMT) Received: from EVON600c (c220-237-7-72.randw1.nsw.optusnet.com.au [220.237.7.72]) (authenticated bits=0) by mail01.syd.optusnet.com.au (8.12.11/8.12.11) with ESMTP id j094ideg019542 for ; Sun, 9 Jan 2005 15:44:43 +1100 Message-Id: <200501090444.j094ideg019542@mail01.syd.optusnet.com.au> From: "Guenzl, Martin" To: Subject: Help with EXPLAIN ANALYZE runtimes Date: Sun, 9 Jan 2005 15:44:30 +1100 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcT2BedwEqD+1YGrR5S3pZKXvVuduQ== X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/93 X-Sequence-Number: 9783 Hi, I am a recent convert to Postgresql, and am trying to tune a very slow query across ten tables all with only a few rows at this stage (<20), and was looking for some help to get me out of a dead-end. It runs very slowly both on a hosted Postgresql 7.3.4 server running on FreeBSD UNIX box, and also on a Postgresql 8.0.0.0-rc2 server running on a Win XP box. On the latter, the EXPLAIN ANALYZE returned what I thought was a strange result - here is the excerpt ... (Start) SQL: Query Results QUERY PLAN Unique (cost=7.16..7.32 rows=3 width=188) (actual time=51.000..51.000 rows=16 loops=1) -> Sort (cost=7.16..7.16 rows=3 width=188) (actual time=51.000..51.000 rows=16 loops=1) Sort Key: am.id_assessment, c.id_claim, c.nm_claim, p.id_provider, p.nm_title, p.nm_first, p.nm_last, ad.id_address, ad.nm_address_1, ad.nm_address_2, ad.nm_address_3, ad.nm_suburb, ad.nm_city, s.nm_state_short, ad.nm_postcode, am.dt_assessment, am.dt_booking, ast.nm_assessmentstatus, ast.b_offer_report, asn.id_assessmentstatus, asn.nm_assessmentstatus -> Merge Join (cost=4.60..7.13 rows=3 width=188) (actual time=41.000..51.000 rows=16 loops=1) Merge Cond: ("outer".id_datastatus = "inner".id_datastatus) Join Filter: (("inner".id_claim = "outer".id_claim) AND ("inner".id_assessment = "outer".id_assessment)) : : : -> Index Scan using address_pkey on address ad (cost=0.00..14.14 rows=376 width=76) (actual time=10.000..10.000 rows=82 loops=1) -> Sort (cost=1.05..1.06 rows=3 width=36) (actual time=0.000..0.000 rows=3 loops=1) Sort Key: am.id_address -> Seq Scan on assessment am (cost=0.00..1.03 rows=3 width=36) (actual time=0.000..0.000 rows=3 loops=1) Total runtime: 51.000 ms 44 row(s) Total runtime: 11,452.979 ms (End) It's the bit at the bottom that throws me - I can't work out why one Total runtime says 51ms, and yet the next Total runtime would be 11,452ms. (I'm assuming that the clue to getting the query time down is to solve this puzzle.) I've done vacuum analyze on all tables, but that didn't help. This query stands out among others as being very slow. Any ideas or suggestions? Thanks in advance, Martin