Re: Performance trouble finding records through related records

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-tp3405914p3423334.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.