From: sverhagen <sverhagen@wps-nl.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance trouble finding records through related records
Date: Thu, 10 Mar 2011 06:10:40 -0800 (PST)
Message-ID: <1299766240195-3423334.post@n5.nabble.com> (raw)
In-Reply-To: <1299024855300-3405914.post@n5.nabble.com>
References: <1299024855300-3405914.post@n5.nabble.com>
Hi, all. I've done some further analysis, found a form that works if I split
things over two separate queries (B1 and B2, below) but still trouble when
combining (B, below).
This is the full pseudo-query: SELECT FROM A UNION SELECT FROM B ORDER BY
dateTime DESC LIMIT 50
In that pseudo-query:
- A is fast (few ms). A is all events for the given customer
- B is slow (1 minute). B is all events for the same transactions as
all events for the given customer
Zooming in on B it looks originally as follows:
SELECT events2.id, events2.transactionId, events2.dateTime FROM
events_events events2
JOIN events_eventdetails details2_transKey
ON events2.id = details2_transKey.event_id
AND details2_transKey.keyname='transactionId'
JOIN events_eventdetails details2_transValue
ON substring(details2_transKey.value,0,32) =
substring(details2_transValue.value,0,32)
AND details2_transValue.keyname='transactionId'
JOIN events_eventdetails customerDetails
ON details2_transValue.event_id = customerDetails.event_id
AND customerDetails.keyname='customer_id'
AND substring(customerDetails.value,0,32)='598124'
WHERE events2.eventtype_id IN (100,103,105,... et cetera ...)
The above version of B is tremendously slow.
The only fast version I've yet come to find is as follows:
- Do a sub-query B1
- Do a sub-query B2 with the results of B1
B1 looks as follows:
Works very fast (few ms)
http://explain.depesz.com/s/7JS
SELECT substring(details2_transValue.value,0,32)
FROM events_eventdetails_customer_id customerDetails
JOIN only events_eventdetails details2_transValue
USING (event_id)
WHERE customerDetails.keyname='customer_id'
AND substring(customerDetails.value,0,32)='49'
AND details2_transValue.keyname='transactionId'
B2 looks as follows:
Works very fast (few ms)
http://explain.depesz.com/s/jGO
SELECT events2.id, events2.dateTime
FROM events_events events2
JOIN events_eventdetails details2_transKey
ON events2.id = details2_transKey.event_id
AND details2_transKey.keyname='transactionId'
AND substring(details2_transKey.value,0,32) IN (... results of B1
...)
AND events2.eventtype_id IN
(100,103,105,106,45,34,14,87,58,78,7,76,11,25,57,98,30,35,33,49,52,28,85,59,23,22,51,48,36,65,66,18,13,86,75,44,38,43,94,56,95,96,71,50,81,90,89,16,17,88,79,77,68,97,92,67,72,53,2,10,31,32,80,24,93,26,9,8,61,5,73,70,63,20,60,40,41,39,101,104,107,99,64,62,55,69,19,46,47,15,21,27,54,12,102,108)
The combined version of B works slow again (3-10 seconds):
http://explain.depesz.com/s/9oM
SELECT events2.id, events2.dateTime
FROM events_events events2
JOIN events_eventdetails details2_transKey
ON events2.id = details2_transKey.event_id
AND details2_transKey.keyname='transactionId'
AND substring(details2_transKey.value,0,32) IN (
SELECT substring(details2_transValue.value,0,32)
FROM events_eventdetails_customer_id customerDetails
JOIN only events_eventdetails details2_transValue
USING (event_id)
WHERE customerDetails.keyname='customer_id'
AND substring(customerDetails.value,0,32)='49'
AND details2_transValue.keyname='transactionId')
AND events2.eventtype_id IN
(100,103,105,106,45,34,14,87,58,78,7,76,11,25,57,98,30,35,33,49,52,28,85,59,23,22,51,48,36,65,66,18,13,86,75,44,38,43,94,56,95,96,71,50,81,90,89,16,17,88,79,77,68,97,92,67,72,53,2,10,31,32,80,24,93,26,9,8,61,5,73,70,63,20,60,40,41,39,101,104,107,99,64,62,55,69,19,46,47,15,21,27,54,12,102,108)
At the moment I see not other conclusion than to offer B1 and B2 to the
database separately, but it feels like defeat :-|
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Performance-trouble-finding-records-through-related-records-...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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: sverhagen@wps-nl.com
Subject: Re: Performance trouble finding records through related records
In-Reply-To: <1299766240195-3423334.post@n5.nabble.com>
* 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