BEGIN CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 200000 INSERT 0 10 INSERT 0 10 CREATE INDEX CREATE SERVER CREATE FOREIGN TABLE CREATE FUNCTION SET SET SET SET SET SET SET SET SET QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Index Scan using foo_a_index on big_table (cost=0.00..2588.27 rows=333 width=8) (actual time=1.488..47.456 rows=65000 loops=1) Index Cond: (a = 2) Filter: (b > 5000) Rows Removed by Filter: 1667 Total runtime: 57.127 ms (5 rows) SET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on big_table (cost=19.86..961.93 rows=333 width=8) (actual time=13.673..67.979 rows=65000 loops=1) Recheck Cond: (a = 2) Rows Removed by Recheck Cond: 62526 Filter: (b > 5000) Rows Removed by Filter: 1667 -> Bitmap Index Scan on foo_a_index (cost=0.00..19.77 rows=1000 width=0) (actual time=12.309..12.309 rows=66667 loops=1) Index Cond: (a = 2) Total runtime: 78.757 ms (8 rows) RESET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on foo (cost=0.00..36.75 rows=713 width=8) (actual time=0.008..0.010 rows=5 loops=1) Filter: (a > 5) Rows Removed by Filter: 5 Total runtime: 0.031 ms (4 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------- Values Scan on "*VALUES*" (cost=0.00..0.15 rows=3 width=4) (actual time=0.021..0.080 rows=5 loops=1) Filter: (column1 > 5) Rows Removed by Filter: 5 Total runtime: 0.189 ms (4 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- CTE Scan on t (cost=3279.93..4052.35 rows=11443 width=4) (actual time=0.020..0.033 rows=5 loops=1) Filter: (a > 5) Rows Removed by Filter: 5 CTE t -> Recursive Union (cost=0.00..3279.93 rows=34330 width=4) (actual time=0.009..0.025 rows=10 loops=1) -> Function Scan on generate_series i (cost=0.00..10.00 rows=1000 width=4) (actual time=0.009..0.009 rows=5 loops=1) -> WorkTable Scan on t (cost=0.00..258.33 rows=3333 width=4) (actual time=0.002..0.003 rows=2 loops=2) Filter: ((a + 5) <= 10) Rows Removed by Filter: 2 Total runtime: 0.080 ms (10 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------- Subquery Scan on ss (cost=0.00..58.15 rows=713 width=8) (actual time=0.008..0.010 rows=5 loops=1) Filter: (ss.i > 5) Rows Removed by Filter: 5 -> Limit (cost=0.00..31.40 rows=2140 width=8) (actual time=0.004..0.004 rows=10 loops=1) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.003..0.003 rows=10 loops=1) Total runtime: 0.056 ms (6 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Function Scan on generate_series i (cost=0.00..12.50 rows=333 width=4) (actual time=0.010..0.012 rows=5 loops=1) Filter: (i > 5) Rows Removed by Filter: 5 Total runtime: 0.026 ms (4 rows) QUERY PLAN --------------------------------------------------------------------------------------------------- Tid Scan on big_table (cost=0.00..4.02 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1) TID Cond: (ctid = '(5,2)'::tid) Filter: (a = 0) Rows Removed by Filter: 1 Total runtime: 0.045 ms (5 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------- Foreign Scan on passwd (cost=0.00..1.92 rows=3 width=176) (actual time=0.005..0.111 rows=31 loops=1) Filter: (uid > 4) Rows Removed by Filter: 5 Foreign File: /etc/passwd Foreign File Size: 1848 Total runtime: 0.184 ms (6 rows) SET QUERY PLAN ------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=0.00..1269372.40 rows=7633 width=12) (actual time=0.163..0.274 rows=5 loops=1) Join Filter: (foo.a = bar.a) Filter: is_even(bar.a) Rows Removed by Filter: 5 -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.003..0.005 rows=10 loops=1) -> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=8) (actual time=0.001..0.004 rows=10 loops=10) Total runtime: 0.304 ms (7 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------ Nested Loop Left Join (cost=0.00..97749.15 rows=7629 width=12) (actual time=0.243..0.331 rows=10 loops=1) Join Filter: (foo.a = public.bar.a) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.023..0.029 rows=10 loops=1) -> Seq Scan on bar (cost=0.00..36.75 rows=713 width=8) (actual time=0.005..0.013 rows=5 loops=10) Filter: (a > 5) Rows Removed by Filter: 5 Total runtime: 0.396 ms (7 rows) SET SET QUERY PLAN -------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=299.56..6378.23 rows=7633 width=12) (actual time=0.070..0.118 rows=5 loops=1) Merge Cond: (foo.a = bar.a) Filter: is_even(bar.a) Rows Removed by Filter: 5 -> Sort (cost=149.78..155.13 rows=2140 width=8) (actual time=0.032..0.034 rows=10 loops=1) Sort Key: foo.a Sort Method: quicksort Memory: 25kB -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.003..0.005 rows=10 loops=1) -> Sort (cost=149.78..155.13 rows=2140 width=8) (actual time=0.011..0.012 rows=10 loops=1) Sort Key: bar.a Sort Method: quicksort Memory: 25kB -> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=8) (actual time=0.001..0.004 rows=10 loops=1) Total runtime: 0.182 ms (13 rows) SET SET QUERY PLAN -------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=58.15..6620.83 rows=7633 width=12) (actual time=0.069..0.111 rows=5 loops=1) Hash Cond: (foo.a = bar.a) Filter: is_even(bar.a) Rows Removed by Filter: 5 -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.005..0.007 rows=10 loops=1) -> Hash (cost=31.40..31.40 rows=2140 width=8) (actual time=0.012..0.012 rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=8) (actual time=0.002..0.006 rows=10 loops=1) Total runtime: 0.152 ms (9 rows) SET QUERY PLAN -------------------------------------------------------------------------------------------------------------- Group (cost=149.78..219.33 rows=214 width=8) (actual time=0.119..0.159 rows=5 loops=1) Filter: is_even(foo.a) Rows Removed by Filter: 5 -> Sort (cost=149.78..155.13 rows=2140 width=8) (actual time=0.082..0.082 rows=10 loops=1) Sort Key: foo.a, foo.b Sort Method: quicksort Memory: 25kB -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.016..0.024 rows=10 loops=1) Total runtime: 0.209 ms (8 rows) SET QUERY PLAN -------------------------------------------------------------------------------------------------------- HashAggregate (cost=42.10..97.74 rows=214 width=8) (actual time=0.033..0.088 rows=5 loops=1) Filter: is_even(foo.a) Rows Removed by Filter: 5 -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=8) (actual time=0.004..0.006 rows=10 loops=1) Total runtime: 0.123 ms (5 rows) SET ROLLBACK