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