test2.sql

application/sql

Download

SET max_parallel_workers_per_gather = 0;
                                                            
DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test (id integer, x numeric, y numeric, z numeric DEFAULT 3.14)
PARTITION BY LIST (id);
CREATE TABLE test_t0 PARTITION OF test FOR VALUES IN (1);
CREATE TABLE test_t1 PARTITION OF test FOR VALUES IN (2);

  
INSERT INTO test (id,x,y)
  SELECT 1, 0.4+random()/10., CASE WHEN random() < 0.0001 THEN -1 ELSE random() END
  FROM generate_series(1,1E6) AS gs;

INSERT INTO test (id,x,y)
  SELECT 2, x, CASE WHEN (x > 0.4 AND x < 0.5) THEN
    CASE WHEN (random() < 0.0001) THEN -1 ELSE random() END
  ELSE random() END
  FROM (SELECT random() AS x FROM generate_series(1,1E7));
  
CREATE INDEX idx1 ON test (x,y);
CREATE INDEX idx2 ON test_t0 (y);
VACUUM ANALYZE test;
VACUUM ANALYZE;

SELECT count(*) FROM test_t0 WHERE y = -1;
SELECT count(*) FROM test_t1 WHERE y = -1;


EXPLAIN (ANALYZE, COSTS ON, BUFFERS ON, TIMING ON)
SELECT * FROM test WHERE x >= 0.4 AND x <= 0.5 AND y = -1
ORDER BY x;
/*
 Sort  (cost=32323.40..32323.40 rows=2 width=32) (actual time=207.772..207.782 rows=203.00 loops=1)
   Sort Key: test.x
   Sort Method: quicksort  Memory: 34kB
   Buffers: shared hit=19 read=5115 written=3913
   ->  Append  (cost=0.42..32323.39 rows=2 width=32) (actual time=0.032..207.659 rows=203.00 loops=1)
         Buffers: shared hit=19 read=5115 written=3913
         ->  Index Scan using idx2 on test_t0 test_1  (cost=0.42..8.45 rows=1 width=32) (actual time=0.032..1.095 rows=104.00 loops=1)
               Index Cond: (y = '-1'::numeric)
               Filter: ((x >= 0.4) AND (x <= 0.5))
               Index Searches: 1
               Buffers: shared hit=5 read=100 written=74
         ->  Index Scan using test_t1_x_y_idx on test_t1 test_2  (cost=0.56..32314.93 rows=1 width=33) (actual time=1.444..206.532 rows=99.00 loops=1)
               Index Cond: ((x >= 0.4) AND (x <= 0.5) AND (y = '-1'::numeric))
               Index Searches: 1
               Buffers: shared hit=14 read=5015 written=3839
 Planning:
   Buffers: shared hit=69 read=6
 Planning Time: 0.696 ms
 Execution Time: 207.819 ms

 Merge Append  (cost=9.03..31240.45 rows=2 width=32) (actual time=10.766..196.565 rows=194.00 loops=1)
   Sort Key: test.x
   Buffers: shared hit=22 read=5104
   ->  Sort  (cost=8.46..8.46 rows=1 width=32) (actual time=2.034..2.049 rows=104.00 loops=1)
         Sort Key: test_1.x
         Sort Method: quicksort  Memory: 29kB
         Buffers: shared hit=6 read=100
         ->  Index Scan using idx2 on test_t0 test_1  (cost=0.42..8.45 rows=1 width=32) (actual time=0.115..1.938 rows=104.00 loops=1)
               Index Cond: (y = '-1'::numeric)
               Filter: ((x >= 0.4) AND (x <= 0.5))
               Index Searches: 1
               Buffers: shared hit=6 read=100
   ->  Index Scan using test_t1_x_y_idx on test_t1 test_2  (cost=0.56..31231.96 rows=1 width=33) (actual time=8.726..194.439 rows=90.00 loops=1)
         Index Cond: ((x >= 0.4) AND (x <= 0.5) AND (y = '-1'::numeric))
         Index Searches: 1
         Buffers: shared hit=16 read=5004
 Planning:
   Buffers: shared hit=40 read=5
 Planning Time: 1.214 ms
 Execution Time: 196.642 ms
*/

EXPLAIN (ANALYZE, COSTS ON, BUFFERS ON, TIMING ON)
SELECT * FROM test WHERE x >= 0.4 AND x <= 0.5 AND y = -1
ORDER BY x LIMIT 100;

/*
 Limit  (cost=1.00..32301.04 rows=100 width=32) (actual time=8.408..131.654 rows=100.00 loops=1)
   Buffers: shared hit=3918
   ->  Merge Append  (cost=1.00..64924.08 rows=201 width=32) (actual time=8.404..131.636 rows=100.00 loops=1)
         Sort Key: test.x
         Buffers: shared hit=3918
         ->  Index Scan using test_t0_x_y_idx on test_t0 test_1  (cost=0.42..33110.40 rows=200 width=32) (actual time=6.224..66.563 rows=53.00 loops=1)
               Index Cond: ((x >= 0.4) AND (x <= 0.5) AND (y = '-1'::numeric))
               Index Searches: 1
               Buffers: shared hit=1954
         ->  Index Scan using test_t1_x_y_idx on test_t1 test_2  (cost=0.56..31811.66 rows=1 width=33) (actual time=2.173..65.007 rows=48.00 loops=1)
               Index Cond: ((x >= 0.4) AND (x <= 0.5) AND (y = '-1'::numeric))
               Index Searches: 1
               Buffers: shared hit=1964
 Planning:
   Buffers: shared hit=8
 Planning Time: 1.122 ms
 Execution Time: 131.755 ms
 
 Limit  (cost=9.03..31240.45 rows=2 width=32) (actual time=3.038..82.779 rows=100.00 loops=1)
   Buffers: shared hit=2825
   ->  Merge Append  (cost=9.03..31240.45 rows=2 width=32) (actual time=3.036..82.768 rows=100.00 loops=1)
         Sort Key: test.x
         Buffers: shared hit=2825
         ->  Sort  (cost=8.46..8.46 rows=1 width=32) (actual time=0.409..0.421 rows=56.00 loops=1)
               Sort Key: test_1.x
               Sort Method: quicksort  Memory: 33kB
               Buffers: shared hit=106
               ->  Index Scan using idx2 on test_t0 test_1  (cost=0.42..8.45 rows=1 width=32) (actual time=0.041..0.284 rows=104.00 loops=1)
                     Index Cond: (y = '-1'::numeric)
                     Filter: ((x >= 0.4) AND (x <= 0.5))
                     Index Searches: 1
                     Buffers: shared hit=106
         ->  Index Scan using test_t1_x_y_idx on test_t1 test_2  (cost=0.56..31231.96 rows=1 width=33) (actual time=2.620..82.307 rows=45.00 loops=1)
               Index Cond: ((x >= 0.4) AND (x <= 0.5) AND (y = '-1'::numeric))
               Index Searches: 1
               Buffers: shared hit=2719
 Planning:
   Buffers: shared hit=8
 Planning Time: 0.453 ms
 Execution Time: 82.853 ms
*/