DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=id6nQPEZ0AYIGCGGl4IkduKFOJ62iFaI8xAtKP/CKzWsrAOKqUjcTRAcvOaj/MdAdc
	26RvWEMGqB4jn7XnXiT5eWtYl1OP3xStYY0UjA5qLudGeijcU4y6l70qko5/oVbrTBeu
	sNiTJlYney16A/Edkl4B+NxkcoDxx5HbhLjWM=
Subject: Re: partitioning question 1
Mime-Version: 1.0 (Apple Message framework v1081)
Content-Type: text/plain; charset=us-ascii
From: Ben <midfield@gmail.com>
In-Reply-To: <1288298912.22359.46.camel@jd-desktop>
Date: Thu, 28 Oct 2010 14:06:57 -0700
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <060B905E-0A4A-4341-9E9F-05CFE15CF71C@gmail.com>
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>
To: jd@commandprompt.com

whoops, didn't see the i=3D9 (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:
>>>=20
>>> My tests show you are incorrect:
>>>=20
>>>=20
>>> part_test=3D# explain analyze select * from foo join bar using (i) =
where
>>> i=3D9;
>>>                                                   QUERY
>>> PLAN                                                   =20
>>> =
--------------------------------------------------------------------------=
----------------------------------------
>>> Nested Loop  (cost=3D34.26..106.76 rows=3D200 width=3D20) (actual
>>> time=3D0.004..0.004 rows=3D0 loops=3D1)
>>>  ->  Append  (cost=3D0.00..68.50 rows=3D20 width=3D12) (actual
>>> time=3D0.004..0.004 rows=3D0 loops=3D1)
>>>        ->  Seq Scan on foo  (cost=3D0.00..34.25 rows=3D10 width=3D12)
>>> (actual time=3D0.001..0.001 rows=3D0 loops=3D1)
>>>              Filter: (i =3D 9)
>>>        ->  Seq Scan on foo_1 foo  (cost=3D0.00..34.25 rows=3D10 =
width=3D12)
>>> (actual time=3D0.000..0.000 rows=3D0 loops=3D1)
>>>              Filter: (i =3D 9)
>>>  ->  Materialize  (cost=3D34.26..34.36 rows=3D10 width=3D12) (never
>>> executed)
>>>        ->  Seq Scan on bar  (cost=3D0.00..34.25 rows=3D10 width=3D12) =
(never
>>> executed)
>>>              Filter: (i =3D 9)
>>> Total runtime: 0.032 ms
>>> (10 rows)
>>=20
>> strange.  my tests don't agree with your tests :
>=20
> Do you have constraint_exclusion turned on? You should verify with =
show
> constraint_exclusion (I saw what you wrote below).
>=20
> JD
>=20
> P.S. Blatant plug, you coming to http://www.postgresqlconference.org ?
>=20
>=20
> --=20
> 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
>=20