From: Ben <midfield@gmail.com>
To: jd@commandprompt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioning question 1
Date: Thu, 28 Oct 2010 14:06:57 -0700
Message-ID: <060B905E-0A4A-4341-9E9F-05CFE15CF71C@gmail.com> (raw)
In-Reply-To: <1288298912.22359.46.camel@jd-desktop>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
<1288287092.22359.23.camel@jd-desktop>
<E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
<1288291814.22359.31.camel@jd-desktop>
<4486C61F-DC08-4DE4-8AF9-245FA60B0310@gmail.com>
<1288295052.22359.35.camel@jd-desktop>
<73315FB7-240B-480C-9200-D547BCA942DE@gmail.com>
<1288298912.22359.46.camel@jd-desktop>
whoops, didn't see the i=9 (linebreak! linebreak!)
nonetheless that is a static constant constraint on the column i, and i was asking if constraint exclusions would work for dynamic constraints (like those derived from a table joined against.) so for example the bar table has only 0-9 in its histogram for i, but constraint exclusion can't use that to eliminate tables foo_2 and foo_3. this is precisely the kind of information an index can use via join selectivity.
i am not going to the pg conference, sorry to say.
b
On Oct 28, 2010, at 1:48 PM, Joshua D. Drake wrote:
> On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
>> On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
>>>
>>> My tests show you are incorrect:
>>>
>>>
>>> part_test=# explain analyze select * from foo join bar using (i) where
>>> i=9;
>>> QUERY
>>> PLAN
>>> ------------------------------------------------------------------------------------------------------------------
>>> Nested Loop (cost=34.26..106.76 rows=200 width=20) (actual
>>> time=0.004..0.004 rows=0 loops=1)
>>> -> Append (cost=0.00..68.50 rows=20 width=12) (actual
>>> time=0.004..0.004 rows=0 loops=1)
>>> -> Seq Scan on foo (cost=0.00..34.25 rows=10 width=12)
>>> (actual time=0.001..0.001 rows=0 loops=1)
>>> Filter: (i = 9)
>>> -> Seq Scan on foo_1 foo (cost=0.00..34.25 rows=10 width=12)
>>> (actual time=0.000..0.000 rows=0 loops=1)
>>> Filter: (i = 9)
>>> -> Materialize (cost=34.26..34.36 rows=10 width=12) (never
>>> executed)
>>> -> Seq Scan on bar (cost=0.00..34.25 rows=10 width=12) (never
>>> executed)
>>> Filter: (i = 9)
>>> Total runtime: 0.032 ms
>>> (10 rows)
>>
>> strange. my tests don't agree with your tests :
>
> Do you have constraint_exclusion turned on? You should verify with show
> constraint_exclusion (I saw what you wrote below).
>
> JD
>
> P.S. Blatant plug, you coming to http://www.postgresqlconference.org ?
>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
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: midfield@gmail.com, jd@commandprompt.com
Subject: Re: partitioning question 1
In-Reply-To: <060B905E-0A4A-4341-9E9F-05CFE15CF71C@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