Skip site navigation (1) Skip section navigation (2)

Re: partitioning question 1

From: Ben <midfield(at)gmail(dot)com>
To: jd(at)commandprompt(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioning question 1
Date: 2010-10-28 21:06:57
Message-ID: 060B905E-0A4A-4341-9E9F-05CFE15CF71C@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
> 


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2010-10-28 21:26:17
Subject: Re: BBU Cache vs. spindles
Previous:From: Joshua D. DrakeDate: 2010-10-28 20:48:32
Subject: Re: partitioning question 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group