2010/5/27 David Jarvis <thangalin@gmail.com>:
> Salut, Cédric.
>
>> I wonder what the plan will be if you replace sc.taken_* in :
>> m.taken BETWEEN sc.taken_start AND sc.taken_end
>> by values. It might help the planner...
>
> That is a fairly important restriction. I will try making it
> (year1||'-01-01')::date, but I have no constant value for it -- it is a
> user-supplied parameter. And then there's the year wrapping problem, too,
> where the ending year will differ from the starting year in certain cases.
> (Like querying rows between Dec 22, 1900 to Mar 22 1901 rather than Mar 22
> 1900 to Dec 22 1900. The first query is the winter season and the second
> query is all seasons except winter.)
Ah, I though that you had a start and an end provided (so able to put
them in the query)
>
>>
>> Also, I'll consider explicit ordered join but I admit I haven't read
>> the whole thread (in particular the table size).
>
> C'est une grosse table. Pres que 40 million lines; il y a sept tableau comme
> ca.
>
> I tried an explicit join in the past: it did not help much. But that was
> before everything was running this fast, so now that the system performs
> differently, maybe it will help?
yes. the documentation is fine for this topic :
http://www.postgresql.org/docs/8.4/interactive/explicit-joins.html
Consider the parameter to explicit join order (you can set it per sql session).
You know your data and know what are the tables with less results to
join first. ;)
>
> Dave
>
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support