Re: [PERFORM] Constraint propagating for equal fields

[Віталій Тимчишин]

30 січня 2010 р. 04:30 Greg Stark gsst...@mit.edu написав:

 2010/1/28 Віталій Тимчишин tiv...@gmail.com
 
  I've always thought that PostgreSQL would propagate constraint from
 field1 to field2 if condition says field1=field2, but this does not seem the
 case:

 version?


PostgreSQL 8.3.7 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1
20070719  [FreeBSD]

Re: [PERFORM] Constraint propagating for equal fields

[Greg Stark]

2010/1/28 Віталій Тимчишин tiv...@gmail.com

 I've always thought that PostgreSQL would propagate constraint from field1 to 
 field2 if condition says field1=field2, but this does not seem the case:

version?

--
greg

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Constraint propagating for equal fields

[Віталій Тимчишин]

Hello.

I've always thought that PostgreSQL would propagate constraint from field1
to field2 if condition says field1=field2, but this does not seem the case:
dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and key  100 and groupid  100;
QUERY PLAN

--
 Hash Join  (cost=2179918.87..4529994.61 rows=4616 width=318)
   Hash Cond: (domain_list.key = domains.groupid)
   -  Bitmap Heap Scan on domain_list  (cost=26253.02..2310541.55
rows=870759 width=123)
 Recheck Cond: (key  100)
 -  Bitmap Index Scan on domain_list_new_pkey  (cost=0.00..26035.33
rows=870759 width=0)
   Index Cond: (key  100)
   -  Hash  (cost=2119232.34..2119232.34 rows=864201 width=195)
 -  Bitmap Heap Scan on domains  (cost=16674.34..2119232.34
rows=864201 width=195)
   Recheck Cond: (groupid  100)
   Filter: (processed_at IS NOT NULL)
   -  Bitmap Index Scan on dgroup  (cost=0.00..16458.29
rows=890154 width=0)
 Index Cond: (groupid  100)
(12 rows)

dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and key  100 ;
   QUERY PLAN


 Hash Join  (cost=2337583.04..18222634.81 rows=845372 width=318)
   Hash Cond: (domains.groupid = domain_list.key)
   -  Seq Scan on domains  (cost=0.00..5423788.20 rows=158280964 width=195)
 Filter: (processed_at IS NOT NULL)
   -  Hash  (cost=2310541.55..2310541.55 rows=870759 width=123)
 -  Bitmap Heap Scan on domain_list  (cost=26253.02..2310541.55
rows=870759 width=123)
   Recheck Cond: (key  100)
   -  Bitmap Index Scan on domain_list_new_pkey
 (cost=0.00..26035.33 rows=870759 width=0)
 Index Cond: (key  100)
(9 rows)

dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and groupid  100;
 QUERY PLAN


 Hash Join  (cost=2153665.85..16943819.35 rows=862710 width=318)
   Hash Cond: (domain_list.key = domains.groupid)
   -  Seq Scan on domain_list  (cost=0.00..6887257.54 rows=162753054
width=123)
   -  Hash  (cost=2119232.34..2119232.34 rows=864201 width=195)
 -  Bitmap Heap Scan on domains  (cost=16674.34..2119232.34
rows=864201 width=195)
   Recheck Cond: (groupid  100)
   Filter: (processed_at IS NOT NULL)
   -  Bitmap Index Scan on dgroup  (cost=0.00..16458.29
rows=890154 width=0)
 Index Cond: (groupid  100)
(9 rows)


The first query is the fastest one, but it is equal to both 2 and 3 and I
thought PostgreSQL can perform such propagation by itself.

Best regards, Vitalii Tymchyshyn.