SQL performance question - Mailing list pgsql-performance

Hello,

This is a performance question that has held me occupied for quite some
time now,

The following join is a somewhat slow query:

(np_artikel, sm_artikel_dim are views and sm_orderrad_* are tables )


xtest=# explain analyze verbose
select * from np_artikel np
join sm_artikel_dim dim on np.artikelid = dim.artikelid
join sm_orderrad ord on ord.artikelid = np.artikelid
JOIN sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr
WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912;

See: http://explain.depesz.com/s/stI

  Total runtime: 47748.786 ms
(140 rows)



This is somewhat strange - beacause i look for i single order-row in a
specific order-batch which only returns one article-id. Please see the
following three questions.




xtest=# SELECT distinct artikelid FROM sm_orderrad ORD JOIN
sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR
= 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912;
  artikelid
-----------
  301206
(1 row)

xtest=# explain analyze verbose SELECT distinct artikelid FROM
sm_orderrad ORD JOIN sm_orderrad_storlek STL ON ORD.ordradnr =
STL.ordradnr WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR = 3 AND
ORD.BATCHNR=3616912;

See: http://explain.depesz.com/s/kI2

  Total runtime: 0.256 ms
(13 rows)

xtest=# explain analyze verbose select * from np_artikel np join
sm_artikel_dim dim on np.artikelid = dim.artikelid where np.artikelid
=301206;

See: http://explain.depesz.com/s/fFN

  Total runtime: 2.563 ms
(99 rows)




Getting the same result from a question where I use a fixed article-id
is about 23 000 times faster .....

Perhaps if use a subquery?




xtest=# explain analyze select * from np_artikel np join sm_artikel_dim
dim on np.artikelid = dim.artikelid where np.artikelid in ( SELECT
distinct artikelid FROM sm_orderrad ORD JOIN sm_orderrad_storlek STL ON
ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR = 3616912 AND STL.ORDRADNR
= 3 AND ORD.BATCHNR=3616912);

See:http://explain.depesz.com/s/wcD )

  Total runtime: 45542.462 ms
(90 rows)



No, not much luck there either ..

CTE's are cool, or so I've heard atleast ...



xtest=# explain analyze verbose
WITH orders AS  ( SELECT distinct artikelid FROM sm_orderrad ORD JOIN
sm_orderrad_storlek STL ON ORD.ordradnr = STL.ordradnr WHERE STL.BATCHNR
= 3616912 AND STL.ORDRADNR = 3 AND ORD.BATCHNR=3616912)

  select * from np_artikel np
  join sm_artikel_dim dim on np.artikelid = dim.artikelid
  join orders on np.artikelid=orders.artikelid;

See: http://explain.depesz.com/s/1a2

  Total runtime: 44966.271 ms
(145 rows)



But they aren't much faster than a join, obviously.

My question is the following: Would it be possible to rewrite the query
in such a way or use some kind of server-setting/tuning so it will get
as fast as when I query with a single article-id as argument?


--
+46 734 307 163 (mobile)
www.lodon.se

Besöksadress:
Lodon AB
Vingalandsgatan 8
417 63 Göteborg