pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: pginfo <pginfo@t1.unisoftbg.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Interest query plan
Date: Tue, 07 Oct 2003 17:36:51 +0200
Message-ID: <3F82DD93.4B3CB117@t1.unisoftbg.com> (raw )
References: <3F82D4FE.DD4E275B@t1.unisoftbg.com >
<3F82E734.8060003@klaster.net >
I have also another good example for a slow left join work.
Can I do it better?
explain analyze select * from a_doc D join A_SKLAD S ON(D.IDS=S.IDS_DOC) join
A_MED M ON(S.IDS_MED=M.IDS) where d
.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=13174.61..112873.53 rows=67002 width=2091) (actual
time=1439.74..86339.93 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Hash Join (cost=13173.35..111699.74 rows=67002 width=2056) (actual
time=1428.01..78454.80 rows=50797 loops=1)
Hash Cond: ("outer".ids_doc = "inner".ids)
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=20.25..61817.66 rows=916555 loops=1)
-> Hash (cost=13145.43..13145.43 rows=11167 width=1344) (actual
time=1399.99..1399.99 rows=0 loops=1)
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.22..1316.10 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=11.18..11.18
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=11.06..11.14 rows=21 loops=1)
Total runtime: 86409.11 msec
(11 rows)
sklad10=# explain analyze select * from a_doc D left outer join A_SKLAD S
ON(D.IDS=S.IDS_DOC) left outer join A_MED M ON(S.IDS_MED=M.IDS) where
d.date_op >= 9600 and d.date_op <= 9700;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=772073.87..778722.53 rows=67002 width=2091) (actual
time=129557.36..142125.53 rows=50797 loops=1)
Hash Cond: ("outer".ids_med = "inner".ids)
-> Merge Join (cost=772072.61..777548.74 rows=67002 width=2056) (actual
time=129556.40..134598.44 rows=50797 loops=1)
Merge Cond: ("outer".ids = "inner".ids_doc)
-> Sort (cost=13896.25..13924.17 rows=11167 width=1344) (actual
time=1403.35..1409.90 rows=9432 loops=1)
Sort Key: d.ids
-> Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167
width=1344) (actual time=0.19..1343.11 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
-> Sort (cost=758176.36..760467.75 rows=916555 width=712) (actual
time=123981.87..127939.17 rows=896110 loops=1)
Sort Key: s.ids_doc
-> Seq Scan on a_sklad s (cost=0.00..83940.55 rows=916555
width=712) (actual time=16.54..66513.61 rows=916555 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.32..0.32
rows=0 loops=1)
-> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual
time=0.20..0.28 rows=21 loops=1)
Total runtime: 142598.55 msec
(14 rows)
sklad10=# explain analyze select * from a_doc D where d.date_op >= 9600 and
d.date_op <= 9700;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on a_doc d (cost=0.00..13145.43 rows=11167 width=1344) (actual
time=0.19..1300.47 rows=9432 loops=1)
Filter: ((date_op >= 9600) AND (date_op <= 9700))
Total runtime: 1309.19 msec
(3 rows)
regards,
ivan.
Tomasz Myrta wrote:
> > 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';
> What about:
>
> select * from a_doc D
> left join A_SKLAD S on(d.IDS=s.IDS_DOC)
> left join A_MED M ON(S.IDS_MED=M.IDS)
> where d.IDS='SOF_700060'
>
> ?
>
> Regards,
> Tomasz Myrta
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
view thread (5+ messages) latest in thread
Message-ID: <3F82DD93.4B3CB117@t1.unisoftbg.com>
Permalink: ../3F82DD93.4B3CB117@t1.unisoftbg.com/
Also on: postgresql.org/message-id/3F82DD93.4B3CB117@t1.unisoftbg.com
copy link · copy postgr.es
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-sql@postgresql.org
Cc: pginfo@t1.unisoftbg.com
Subject: Re: Interest query plan
In-Reply-To: <3F82DD93.4B3CB117@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