Message-ID: <50AE1529.3050000@lodon.se>
Date: Thu, 22 Nov 2012 13:06:01 +0100
From: Niklas Paulsson <niklas.paulsson@lodon.se>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: postgresql-perf <pgsql-performance@postgresql.org>
Subject: SQL performance question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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