On 03/01/2011 06:14 PM, sverhagen wrote:
Hi, appreciated mailing list. Thanks already for taking your time for my
performance question. Regards, Sander.
===POSTGRESQL VERSION AND ORIGIN===
PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
(Ubuntu 4.2.4-1ubuntu3)
Installed using "apt-get install postgresql-8.3"
===A DESCRIPTION OF WHAT YOU ARE TRYING TO ACHIEVE===
Query involving tables events_events and events_eventdetails. There is any
number of events_eventdetails records for each events_events record.
There may be multiple records in events_events that have the same value for
their transactionId, which is available in one of their events_eventdetails
records.
We want a total query that returns events_events records that match
condition I. or II., sorted by datetime descending, first 50.
Condition I.
All events_events records for which an events_eventdetails records that
matches the following conditions:
- Column keyname (in events_eventdetails) equals "customerId", and
- Column value (in events_eventdetails) equals 598124, or more precisely
substring(customerDetails.value,0,32)='598124'
Condition II.
All events_events records that have a same value for in one of their
events_eventdetails records with keyname 'transactionId' as any of the
resulting events_events records of condition I.
In other words: I want all events for a certain customerId, and all events
with the same transactionId as those.
The total query's time should be of the magnitude 100ms, but currently is of
the magnitude 1min.
JUST FOR THE PURPOSE OF EXPERIMENT I've now a denormalized copy of
transactionId as a column in the events_events records. Been trying queries
on those, with no improvements.
I am not seeking WHY my query is too slow, rather trying to find a way to
get it faster :-)
<much snippage>
First off, excellent detail.
Second, your explain analyze was hard to read... but since you are not really
interested in your posted query, I wont worry about looking at it... but...
have you seen:
http://explain.depesz.com/
Its nice.
And last, to my questions:
SELECT events1.id, events1.transactionId, events1.dateTime FROM
events_events events1
JOIN events_eventdetails customerDetails
ON events1.id = customerDetails.event_id
AND customerDetails.keyname='customer_id'
AND substring(customerDetails.value,0,32)='598124'
WHERE events1.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)
UNION
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,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)
ORDER BY dateTime DESC LIMIT 50
If you run the individual queries, without the union, are the part's slow too?
Looked like your row counts (the estimate vs the actual) were way off, have you
analyzed lately?
I could not tell from the explain analyze if an index was used, but I notice
you have a ton of indexes on events_events table. You have two indexes on the
same fields but in reverse order:
events_events_eventtype_id_datetime_ind (datetime, eventtype_id);
events_events_datetime_eventtype_id_ind (eventtype_id, datetime);
AND both eventtype_id and datetime are in other indexes! I think you need to
review your indexes. Drop all of them and add one or two that are actually
useful.
A useful tool I have found for complex queries is to break them down into
smaller sub sets, write sql that get's me just those sets, and them add them
all back into one main query with subselects:
select a,b,c,...
from events_events
where
id in ( select id from details where some subset is needed )
and id not in ( select id frome details where some set is bad )
and id in ( select anotherid from anothertable where ... )
Its the subselects you need to think about. Find one that gets you a small set
that's interesting somehow. Once you get all your little sets, its easy to
combine them.
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
