From: Cédric Villemain <cedric.villemain.debian@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Vlad Arkhipov <arhipov@dc.baikal.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimization idea
Date: Wed, 28 Apr 2010 09:49:05 +0200
Message-ID: <z2te94e14cd1004280049i9f8d6b90u61dac137eede515d@mail.gmail.com> (raw)
In-Reply-To: <q2w603c8f071004271746s43f8669cz45bec5914b1fa9e0@mail.gmail.com>
References: <4BD0161A.4010908@dc.baikal.ru>
<4BD0430C.1050308@2ndquadrant.com>
<4BD10805.9060004@dc.baikal.ru>
<y2y603c8f071004230405r5fcef546l3da84c407ce2655a@mail.gmail.com>
<4BD4FFE7.9090305@dc.baikal.ru>
<x2te94e14cd1004260233i45c44966qc4a8b10f4edb2963@mail.gmail.com>
<q2w603c8f071004271746s43f8669cz45bec5914b1fa9e0@mail.gmail.com>
2010/4/28 Robert Haas <robertmhaas@gmail.com>:
> On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
> <cedric.villemain.debian@gmail.com> wrote:
>> In the first query, the planner doesn't use the information of the 2,3,4.
>> It just does a : I'll bet I'll have 2 rows in t1 (I think it should
>> say 3, but it doesn't)
>> So it divide the estimated number of rows in the t2 table by 5
>> (different values) and multiply by 2 (rows) : 40040.
>
> I think it's doing something more complicated. See scalararraysel().
Thank you for driving me to the right function, Robert.
It is in fact more complicated :)
>
>> In the second query the planner use a different behavior : it did
>> expand the value of t1.t to t2.t for each join relation and find a
>> costless plan. (than the one using seqscan on t2)
>
> I think the problem here is one we've discussed before: if the query
> planner knows that something is true of x (like, say, x =
> ANY('{2,3,4}')) and it also knows that x = y, it doesn't infer that
> the same thing holds of y (i.e. y = ANY('{2,3,4}') unless the thing
> that is known to be true of x is that x is equal to some constant.
> Tom doesn't think it would be worth the additional CPU time that it
> would take to make these sorts of deductions. I'm not sure I believe
> that, but I haven't tried to write the code, either.
If I understand correctly, I did have some issues with
exclusion_constraint= ON for complex queries in datamining where the
planner failled to understand it must use only one partition because
the where clause where not enough 'explicit'. But it's long time ago
and I don't have my use case.
We probably need to find some real case where the planner optimisation
make sense. But I don't want usual queries to see their CPU time
increase...
<joke>Do we need real Planner Hints ?</joke>
--
Cédric Villemain
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: cedric.villemain.debian@gmail.com, robertmhaas@gmail.com, arhipov@dc.baikal.ru
Subject: Re: Optimization idea
In-Reply-To: <z2te94e14cd1004280049i9f8d6b90u61dac137eede515d@mail.gmail.com>
References: <4BD0161A.4010908@dc.baikal.ru> <4BD0430C.1050308@2ndquadrant.com> <4BD10805.9060004@dc.baikal.ru> <y2y603c8f071004230405r5fcef546l3da84c407ce2655a@mail.gmail.com> <4BD4FFE7.9090305@dc.baikal.ru> <x2te94e14cd1004260233i45c44966qc4a8b10f4edb2963@mail.gmail.com> <q2w603c8f071004271746s43f8669cz45bec5914b1fa9e0@mail.gmail.com> <z2te94e14cd1004280049i9f8d6b90u61dac137eede515d@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