Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 5A89A1337999 for ; Thu, 31 Mar 2011 21:53:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 82747-03 for ; Fri, 1 Apr 2011 00:53:03 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 233F31337B82 for ; Thu, 31 Mar 2011 21:53:01 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.4/8.14.4) with ESMTP id p310r0aY000961 for ; Thu, 31 Mar 2011 21:53:00 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.4/8.14.4/Submit) id p310f4xf070620 for pgsql-performance@postgresql.org; Thu, 31 Mar 2011 21:41:04 -0300 (ADT) (envelope-from news) From: Joseph Shraibman X-Newsgroups: pgsql.performance Subject: table contraints checks only happen in planner phase Date: Thu, 31 Mar 2011 20:41:04 -0400 Organization: Hub.Org Networking Services Lines: 139 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.15) Gecko/20110307 Fedora/3.1.9-0.38.b3pre.fc13 Thunderbird/3.1.9 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/383 X-Sequence-Number: 43062 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)