test.sql
application/sql
Filename: test.sql
Type: application/sql
Part: 0
SET enable_hashjoin TO off;
SET enable_mergejoin TO off;
SET enable_bitmapscan TO off;
DROP TABLE IF EXISTS test,test1 CASCADE;
CREATE TABLE test (x numeric, t text, x1 real, t1 real);
INSERT INTO test (x,t,x1,t1) SELECT d1::numeric, d1::text, d1::real, d1::real FROM (
SELECT round((d/pi())::numeric,7) AS d1 FROM generate_series(1,100) AS d);
INSERT INTO test SELECT * FROM test;
CREATE INDEX idx_x ON test (x);
CREATE INDEX idx_t ON test (t);
CREATE INDEX idx_x1 ON test (x1);
CREATE INDEX idx_t1 ON test (t1);
VACUUM ANALYZE test;
EXPLAIN (ANALYZE, COSTS OFF) -- ethalon for logical
SELECT * FROM test t1 INNER JOIN test t2
ON t1.x1=t2.t1 AND t1.t1=t2.x1
WHERE t1.x1<5;
EXPLAIN (ANALYZE, COSTS OFF) -- ethalon for binary
SELECT * FROM test t1 INNER JOIN test t2
ON t1.x1=t2.t1 AND t1.t1>=t2.x1
WHERE t1.x1<5;
EXPLAIN (ANALYZE, COSTS OFF) -- logical mode
SELECT * FROM test t1 INNER JOIN test t2
ON t1.x=t2.t::numeric AND t1.t::numeric=t2.x
WHERE t1.x < 5;
EXPLAIN (ANALYZE, COSTS OFF) -- binary mode
SELECT * FROM test t1 INNER JOIN test t2
ON t1.x=t2.t::numeric AND t1.t::numeric>=t2.x
WHERE t1.x < 5;
/*
-- Current master
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.053..0.265 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.013..0.059 rows=30 loops=1)
Filter: (x1 < '5'::double precision)
Rows Removed by Filter: 170
-> Memoize (actual time=0.004..0.005 rows=2 loops=30)
Cache Key: t1.x1, t1.t1
Cache Mode: logical
Hits: 15 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using idx_t1 on test t2 (actual time=0.006..0.007 rows=2 loops=15)
Index Cond: (t1 = t1.x1)
Filter: (t1.t1 = x1)
Planning Time: 0.753 ms
Execution Time: 0.340 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.028..0.231 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.011..0.056 rows=30 loops=1)
Filter: (x1 < '5'::double precision)
Rows Removed by Filter: 170
-> Memoize (actual time=0.003..0.005 rows=2 loops=30)
Cache Key: t1.t1, t1.x1
Cache Mode: binary
Hits: 15 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using idx_t1 on test t2 (actual time=0.004..0.006 rows=2 loops=15)
Index Cond: (t1 = t1.x1)
Filter: (t1.t1 >= x1)
Planning Time: 0.350 ms
Execution Time: 0.288 ms
(13 rows)
QUERY PLAN
------------------------------------------------------------------------------------------
Nested Loop (actual time=0.056..0.600 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.012..0.100 rows=30 loops=1)
Filter: (x < '5'::numeric)
Rows Removed by Filter: 170
-> Memoize (actual time=0.012..0.015 rows=2 loops=30)
Cache Key: t1.x, (t1.t)::numeric
Cache Mode: logical
Hits: 0 Misses: 30 Evictions: 0 Overflows: 0 Memory Usage: 8kB
-> Index Scan using idx_x on test t2 (actual time=0.008..0.010 rows=2 loops=30)
Index Cond: (x = (t1.t)::numeric)
Filter: (t1.x = (t)::numeric)
Planning Time: 0.391 ms
Execution Time: 0.653 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.033..0.721 rows=60 loops=1)
-> Seq Scan on test t2 (actual time=0.009..0.045 rows=200 loops=1)
-> Memoize (actual time=0.003..0.003 rows=0 loops=200)
Cache Key: t2.x, (t2.t)::numeric
Cache Mode: binary
Hits: 100 Misses: 100 Evictions: 0 Overflows: 0 Memory Usage: 16kB
-> Index Scan using idx_x on test t1 (actual time=0.001..0.002 rows=0 loops=100)
Index Cond: ((x = (t2.t)::numeric) AND (x < '5'::numeric))
Filter: ((t)::numeric >= t2.x)
Planning Time: 0.268 ms
Execution Time: 0.777 ms
(11 rows)
*/
/*
-- After the patch applied:
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.089..0.440 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.022..0.102 rows=30 loops=1)
Filter: (x1 < '5'::double precision)
Rows Removed by Filter: 170
-> Memoize (actual time=0.007..0.009 rows=2 loops=30)
Cache Key: t1.x1, t1.t1
Cache Mode: logical
Hits: 15 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using idx_t1 on test t2 (actual time=0.010..0.012 rows=2 loops=15)
Index Cond: (t1 = t1.x1)
Filter: (t1.t1 = x1)
Planning Time: 1.132 ms
Execution Time: 0.556 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.041..0.358 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.016..0.084 rows=30 loops=1)
Filter: (x1 < '5'::double precision)
Rows Removed by Filter: 170
-> Memoize (actual time=0.005..0.007 rows=2 loops=30)
Cache Key: t1.t1, t1.x1
Cache Mode: binary
Hits: 15 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using idx_t1 on test t2 (actual time=0.008..0.010 rows=2 loops=15)
Index Cond: (t1 = t1.x1)
Filter: (t1.t1 >= x1)
Planning Time: 0.554 ms
Execution Time: 0.434 ms
(13 rows)
QUERY PLAN
------------------------------------------------------------------------------------------
Nested Loop (actual time=0.093..0.627 rows=60 loops=1)
-> Seq Scan on test t1 (actual time=0.020..0.141 rows=30 loops=1)
Filter: (x < '5'::numeric)
Rows Removed by Filter: 170
-> Memoize (actual time=0.011..0.014 rows=2 loops=30)
Cache Key: t1.x, (t1.t)::numeric
Cache Mode: logical
Hits: 15 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using idx_x on test t2 (actual time=0.014..0.018 rows=2 loops=15)
Index Cond: (x = (t1.t)::numeric)
Filter: (t1.x = (t)::numeric)
Planning Time: 0.624 ms
Execution Time: 0.727 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (actual time=0.043..1.152 rows=60 loops=1)
-> Seq Scan on test t2 (actual time=0.011..0.071 rows=200 loops=1)
-> Memoize (actual time=0.004..0.004 rows=0 loops=200)
Cache Key: t2.x, (t2.t)::numeric
Cache Mode: binary
Hits: 100 Misses: 100 Evictions: 0 Overflows: 0 Memory Usage: 10kB
-> Index Scan using idx_x on test t1 (actual time=0.002..0.003 rows=0 loops=100)
Index Cond: ((x = (t2.t)::numeric) AND (x < '5'::numeric))
Filter: ((t)::numeric >= t2.x)
Planning Time: 0.386 ms
Execution Time: 1.242 ms
(11 rows)
*/