Hi,

We again have problems with query planer... (Ubuntu, pg 9.1)

Up to now - solution was "rephrase the question", but for next thing we are
not sure what would be best solution...

the whole story is too complex... but simplified:

We have tables:

things (thing_id int pk... other columns...)
activities (activity_id int pk, date, thing_id.... other columns...)

So, for each day we track main activities about things...

Now... each activity... could have 0 or more additional info about
activity... if that happened at all that day...

So we have:
additional_activities (id serial pk, activity_id int fk,... other
columns...)


Now, what creates problems...

We need a view
what shows all info about things and activities...

but just 1 row per activity...

so:

date, thing columns, activity columns... and now last 7 columns are from
additional_activities table... what can have 0 or more rows related to the
activity - but we need just one...
if it has more than 1 row - we should show:
-actual values from the first row (related to the activity) + last two
columns: sum value and total number of additinal info relateed to the
activity...

So we have make a view:


WITH main_id AS (
         SELECT min(id) AS id, sum(value) AS total_value, count(1) AS
total_additional_info
           FROM additional_activities
          GROUP BY activity_id
        )
 SELECT *
   FROM main_id
   JOIN additional_activities USING (id);


What actually returns first row values about thing + summarized  values...

then left join to that view - and we get result what we want...

with my_view:

SELECT *
FROM things
JOIN activities USING (thing_id)
LEFT JOIN additional_activities_view USING (thing_id)


Usual query on that view is:

SELECT * FROM my_view WHERE thing_id = $1 AND date BETWEEN $2 AND $3

And now comes problems:
Query1:
SELECT * FROM my_view WHERE thing_id = 321 AND date BETWEEN '20130301' AND
'20130331'

takes more then 20s and uses very bad plan:
http://explain.depesz.com/s/CLh

but Query2:
SELECT * FROM my_view WHERE thing_id = 321 AND date BETWEEN '20130201' AND
'20130331

What returns even more rows then query1,  (Changed just from date 1st Feb
instead of 1st March)

takes less then 2 seconds!?
http://explain.depesz.com/s/9QP


Any suggestions?

Many Thanks,

Misa

Reply via email to