Thread: Interest query plan
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 ona_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.52rows=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=6width=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.
> 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
explain analyze select * from a_doc D left outer 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'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1.26..80.55 rows=6 width=2091) (actual time=1.09..1.11 rows=1 loops=1) Hash Cond: ("outer".ids_med = "inner".ids) -> Nested Loop (cost=0.00..79.18 rows=6 width=2056) (actual time=0.40..0.41 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.14..0.14 rows=1 loops=1) Index Cond: (ids = 'SOF_700060'::name) -> IndexScan using i_sklad_ids_doc on a_sklad s (cost=0.00..75.31 rows=22 width=712) (actual time=0.12..0.13 rows=1 loops=1) Index Cond: ("outer".ids = s.ids_doc) -> Hash (cost=1.21..1.21 rows=21 width=35) (actual time=0.19..0.19 rows=0 loops=1) -> Seq Scan on a_med m (cost=0.00..1.21 rows=21 width=35) (actual time=0.07..0.15 rows=21 loops=1)Total runtime: 1.82 msec (10 rows) I thinked that a_sklad join a_med ... will help, but.... 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
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.74rows=67002 width=2056) (actual time=1428.01..78454.80 rows=50797 loops=1) Hash Cond: ("outer".ids_doc = "inner".ids) -> Seq Scan on a_sklads (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.74rows=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.17rows=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.43rows=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.55rows=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
> 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; > -> 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) I wouldn't expect too much from query, which starts joining over 10k rows and returns over 60000 rows. Do you really need such a big result? Regards, Tomasz Myrta