From: Cédric Villemain <cedric.villemain.debian@gmail.com>
To: David Jarvis <thangalin@gmail.com>
Cc: Bryan Hinton <bryan@bryanhinton.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Random Page Cost and Planner
Date: Thu, 27 May 2010 20:28:36 +0200
Message-ID: <AANLkTinuOUTbuke9FTMtwSObcZBio0uOS1ysWrqFyK3t@mail.gmail.com> (raw)
In-Reply-To: <AANLkTinAhrwwwQKh6NrX-nnvcpxT1EFFlZrUxhaDB87C@mail.gmail.com>
References: <AANLkTikS_QvlkYfv5_hbsXFH4SsTh1nP_WoTwRqpUUcz@mail.gmail.com>
<AANLkTikegoyBvJXhqZGLZyTwRVyBs4qCuCQ7d1Vntk4w@mail.gmail.com>
<AANLkTikjkEvj2hG-D1CMu49PQXXbxHVzpXm9JbLWzMQo@mail.gmail.com>
<AANLkTimtdk11hrWd-OpvXGLrh-4pukGd9eHEUemEM3OR@mail.gmail.com>
<AANLkTinAhrwwwQKh6NrX-nnvcpxT1EFFlZrUxhaDB87C@mail.gmail.com>
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
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: cedric.villemain.debian@gmail.com, thangalin@gmail.com, bryan@bryanhinton.com
Subject: Re: Random Page Cost and Planner
In-Reply-To: <AANLkTinuOUTbuke9FTMtwSObcZBio0uOS1ysWrqFyK3t@mail.gmail.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