From: pginfo <pginfo@t1.unisoftbg.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Interest query plan
Date: Tue, 07 Oct 2003 17:00:14 +0200
Message-ID: <3F82D4FE.DD4E275B@t1.unisoftbg.com> (raw)
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.
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-sql@postgresql.org
Cc: pginfo@t1.unisoftbg.com
Subject: Re: Interest query plan
In-Reply-To: <3F82D4FE.DD4E275B@t1.unisoftbg.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