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

Constraint propagating for equal fields

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Constraint propagating for equal fields
Date: 2010-01-28 11:21:01
Message-ID: 331e40661001280321ka774d66j8594e2b3fb2c2b8b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 < 1000000 and groupid < 1000000;
                                            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 < 1000000)
         ->  Bitmap Index Scan on domain_list_new_pkey  (cost=0.00..26035.33
rows=870759 width=0)
               Index Cond: (key < 1000000)
   ->  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 < 1000000)
               Filter: (processed_at IS NOT NULL)
               ->  Bitmap Index Scan on dgroup  (cost=0.00..16458.29
rows=890154 width=0)
                     Index Cond: (groupid < 1000000)
(12 rows)

dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and key < 1000000 ;
                                               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 < 1000000)
               ->  Bitmap Index Scan on domain_list_new_pkey
 (cost=0.00..26035.33 rows=870759 width=0)
                     Index Cond: (key < 1000000)
(9 rows)

dict=# explain select * from domain_list,title.domains where processed_at is
not null and key=groupid and groupid < 1000000;
                                         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 < 1000000)
               Filter: (processed_at IS NOT NULL)
               ->  Bitmap Index Scan on dgroup  (cost=0.00..16458.29
rows=890154 width=0)
                     Index Cond: (groupid < 1000000)
(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.

Responses

pgsql-performance by date

Next:From: **Rod MacNeilDate: 2010-01-29 16:37:55
Subject: Limited Shared Buffer Problem
Previous:From: Ivan VorasDate: 2010-01-28 10:52:09
Subject: Re: Benchmark shows very slow bulk delete

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