Re: Interest query plan

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)
-> Index Scan 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