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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
