From: Niklas Paulsson <niklas.paulsson@lodon.se>
To: postgresql-perf <pgsql-performance@postgresql.org>
Subject: SQL performance question
Date: Thu, 22 Nov 2012 13:06:01 +0100
Message-ID: <50AE1529.3050000@lodon.se> (raw)
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
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-performance@postgresql.org
Cc: niklas.paulsson@lodon.se
Subject: Re: SQL performance question
In-Reply-To: <50AE1529.3050000@lodon.se>
* 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