Thread

1. table contraints checks only happen in planner phase

   Joseph Shraibman <jks@selectacast.net> — 2011-04-01T00:41:04Z

   Is there a reason that when executing queries the table constraints are
   only checked during planning and not execution?  I end up making 2 round
   trips to the database to get around this.
   
   All of these queries should produce the same output:
   
    
   [local]:playpen=>  explain analyze select count(*) from vis where seg = 69;
                                                                       
   QUERY
   PLAN                                                                    
   ----------------------------------------------------------------------------------------------------------------------------------------------------
    Aggregate  (cost=857.51..857.52 rows=1 width=0) (actual
   time=16.551..16.553 rows=1 loops=1)
      ->  Append  (cost=72.70..849.62 rows=3155 width=0) (actual
   time=0.906..12.754 rows=3154 loops=1)
            ->  Bitmap Heap Scan on vis  (cost=72.70..838.12 rows=3154
   width=0) (actual time=0.903..6.346 rows=3154 loops=1)
                  Recheck Cond: (seg = 69)
                  ->  Bitmap Index Scan on vis_seg_firstevent_idx 
   (cost=0.00..71.91 rows=3154 width=0) (actual time=0.787..0.787 rows=3154
   loops=1)
                        Index Cond: (seg = 69)
            ->  Seq Scan on vis_for_seg_69_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                  Filter: (seg = 69)
    Total runtime: 16.702 ms
   (9 rows)
   
   Time: 27.581 ms
   [local]:playpen=>
   [local]:playpen=> explain analyze select count(*) from vis where seg =
   (select seg from an where key = 471);
                                                                      
   QUERY
   PLAN                                                                    
   ---------------------------------------------------------------------------------------------------------------------------------------------------
    Aggregate  (cost=713.50..713.51 rows=1 width=0) (actual
   time=16.721..16.722 rows=1 loops=1)
      InitPlan 1 (returns $0)
        ->  Index Scan using an_pkey on an  (cost=0.00..8.27 rows=1
   width=4) (actual time=0.037..0.041 rows=1 loops=1)
              Index Cond: (key = 471)
      ->  Append  (cost=10.92..704.35 rows=352 width=0) (actual
   time=0.970..13.024 rows=3154 loops=1)
            ->  Bitmap Heap Scan on vis  (cost=10.92..612.35 rows=344
   width=0) (actual time=0.967..6.470 rows=3154 loops=1)
                  Recheck Cond: (seg = $0)
                  ->  Bitmap Index Scan on vis_seg_firstevent_idx 
   (cost=0.00..10.83 rows=344 width=0) (actual time=0.862..0.862 rows=3154
   loops=1)
                        Index Cond: (seg = $0)
            ->  Seq Scan on vis_for_seg_1_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_4_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_66_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_69_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_79_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_80_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_82_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                  Filter: (seg = $0)
            ->  Seq Scan on vis_for_seg_87_2011_03 vis  (cost=0.00..11.50
   rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                  Filter: (seg = $0)
    Total runtime: 17.012 ms
   (26 rows)
   
   Time: 24.147 ms
   [local]:playpen=>
   [local]:playpen=> explain analyze select count(vis.*) from vis, an where
   vis.seg = an.seg and an.key = 471;
                                                                         
   QUERY
   PLAN                                                                       
   ---------------------------------------------------------------------------------------------------------------------------------------------------------
    Aggregate  (cost=726.72..726.73 rows=1 width=29) (actual
   time=30.061..30.062 rows=1 loops=1)
      ->  Nested Loop  (cost=10.92..725.65 rows=424 width=29) (actual
   time=0.999..26.118 rows=3154 loops=1)
            Join Filter: (public.vis.seg = an.seg)
            ->  Index Scan using an_pkey on an  (cost=0.00..8.27 rows=1
   width=4) (actual time=0.024..0.032 rows=1 loops=1)
                  Index Cond: (key = 471)
            ->  Append  (cost=10.92..701.09 rows=1304 width=36) (actual
   time=0.938..18.488 rows=3154 loops=1)
                  ->  Bitmap Heap Scan on vis  (cost=10.92..611.49 rows=344
   width=36) (actual time=0.936..11.753 rows=3154 loops=1)
                        Recheck Cond: (public.vis.seg = an.seg)
                        ->  Bitmap Index Scan on vis_seg_firstevent_idx 
   (cost=0.00..10.83 rows=344 width=0) (actual time=0.826..0.826 rows=3154
   loops=1)
                              Index Cond: (public.vis.seg = an.seg)
                  ->  Seq Scan on vis_for_seg_1_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.003..0.003 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_4_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.001..0.001 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_66_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.002..0.002 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_69_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.002..0.002 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_79_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.002..0.002 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_80_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.001..0.001 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_82_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.002..0.002 rows=0
   loops=1)
                  ->  Seq Scan on vis_for_seg_87_2011_03 vis 
   (cost=0.00..11.20 rows=120 width=36) (actual time=0.002..0.002 rows=0
   loops=1)
    Total runtime: 30.398 ms
   (19 rows)
   
   
   [local]:playpen=> select version();
                                                    
   version                                                  
   ------------------------------------------------------------------------------------------------------------
    PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
   20080704 (Red Hat 4.1.2-48), 32-bit
   (1 row)
   
   

2. Re: table contraints checks only happen in planner phase

   Josh Berkus <josh@agliodbs.com> — 2011-04-01T17:32:24Z

   On 3/31/11 5:41 PM, Joseph Shraibman wrote:
   > Is there a reason that when executing queries the table constraints are
   > only checked during planning and not execution?  I end up making 2 round
   > trips to the database to get around this.
   
   This is a limitation with our current partitioning implementation.  It
   only understands literal values, not JOINs.
   
   -- 
   Josh Berkus
   PostgreSQL Experts Inc.
   http://pgexperts.com