X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6FF63D1B4ED for ; Tue, 7 Oct 2003 16:09:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 37578-10 for ; Tue, 7 Oct 2003 13:08:42 -0300 (ADT) Received: from unimm.unisoftbg.com (mail.unisoftbg.com [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id E2107D1B4E5 for ; Tue, 7 Oct 2003 13:08:38 -0300 (ADT) Received: (qmail 30207 invoked by uid 507); 7 Oct 2003 16:12:47 -0000 Received: from unknown (HELO t1.unisoftbg.com) (pginfo%t1.unisoftbg.com@194.12.229.193) by 0 with SMTP; 7 Oct 2003 16:12:47 -0000 Message-ID: <3F82D4FE.DD4E275B@t1.unisoftbg.com> Date: Tue, 07 Oct 2003 17:00:14 +0200 From: pginfo X-Mailer: Mozilla 4.03 [en] (Win95; I) MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Interest query plan Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/79 X-Sequence-Number: 15479 Hi all, I am running pg 7.3.1. My query is very simple but pg generates not the best possible plan for me: analyze select * from a_doc D left outer join (A_SKLAD S join A_MED M ON(S.IDS_MED=M.IDS) )on( d.IDS=s.IDS_DOC) where d.IDS='SOF_700060'; The plan is: --------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1.26..111442.07 rows=6 width=2091) (actual time=99512.48..101105.48 rows=1 loops=1) Join Filter: ("outer".ids = "inner".ids_doc) -> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1 width=1344) (actual time=0.13..0.14 rows=1 loops=1) Index Cond: (ids = 'SOF_700060'::name) -> Materialize (cost=99981.52..99981.52 rows=916555 width=747) (actual time=96980.73..99907.73 rows=916555 loops=1) -> Hash Join (cost=1.26..99981.52 rows=916555 width=747) (actual time=9.34..86400.88 rows=916555 loops=1) Hash Cond: ("outer".ids_med = "inner".ids) -> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555 width=712) (actual time=0.17..45881.02 rows=916555 loops=1) -> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=8.79..8.79 rows=0 loops=1) -> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual time=8.68..8.75 rows=21 loops=1) Total runtime: 101563.40 msec (11 rows) I think the best olution will be first to left join a_doc and a_sklad and after it to join a_sklad and a_med. Can I force pg to execute this query better? If I do not use left join, the query is very fast: explain analyze select * from a_doc D,A_SKLAD S,A_MED M where d.IDS=s. IDS_DOC AND S.IDS_MED=M.IDS AND d.IDS='SOF_700160'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual time=20.41..20.46 rows=1 loops=1) Hash Cond: ("outer".ids_med = "inner".ids) -> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual time=19.23..19.26 rows=1 loops=1) -> Index Scan using a_doc_pkey on a_doc d (cost=0.00..3.61 rows=1 width=1344) (actual time=0.59..0.60 rows=1 loops=1) Index Cond: (ids = 'SOF_700160'::name) -> Index Scan using i_sklad_ids_doc on a_sklad s (cost=0.00..75.31 rows=22 width=712) (actual time=18.25..18.26 rows=1 loops=1) Index Cond: ("outer".ids = s.ids_doc) -> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.36..0.36 rows=0 loops=1) -> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual time=0.22..0.30 rows=21 loops=1) Total runtime: 21.27 msec (10 rows) But I think it is very big penalty for this left join. regards, ivan.