Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE test
- (
- id INT,
- descr TEXT,
- flag BOOLEAN
- );
- INSERT INTO test
- SELECT GENERATE_SERIES(1,100000) AS id,
- MD5(RANDOM()::TEXT) AS descr,
- (RANDOM() < 0.1) AS flag;
- SELECT *
- FROM test LIMIT 10;
- id descr flag
- 1 81978ceb5514461fbad9af1152ad78f6 true
- 2 cc0aee68ba3e0095cc74d53e8da55fef false
- 3 689a76e5897d565638f8ddd2d2019b7a true
- 4 9df03bc2969a6af88cd1d6e0423d0f4c true
- 5 318983766d11f831e9f0df34606dc908 false
- 6 198102bb71640a16f28263b7fb56ba2e false
- 7 9bef7320389db46a8ad88ffa611e81b5 false
- 8 c1f0d637ee0a985aa7d768a78d2d97b1 false
- 9 781b4064f721ae3879d95579264b0aba false
- 10 c4582890bb1e9af430e0f36b50f5e88c false
- SELECT id
- FROM test
- WHERE flag;
- CREATE INDEX i1
- ON test (id) WHERE flag;
- QUERY PLAN
- Index Only Scan using i1 on test (cost=0.29..354.95 rows=9911 width=4) (actual time=0.120..6.268 rows=9911 loops=1)
- Heap Fetches: 9911
- Buffers: shared hit=834 read=29
- Planning time: 0.806 ms
- Execution time: 6.922 ms
- CREATE INDEX i2
- ON test (flag, id);
- QUERY PLAN
- Bitmap Heap Scan on test (cost=189.10..1122.21 rows=9911 width=4) (actual time=0.767..5.986 rows=9911 loops=1)
- Filter: flag
- Heap Blocks: exact=834
- Buffers: shared hit=863
- -> Bitmap Index Scan on i2 (cost=0.00..186.62 rows=9911 width=0) (actual time=0.669..0.669 rows=9911 loops=1)
- Index Cond: (flag = true)
- Buffers: shared hit=29
- Planning time: 0.090 ms
- Execution time: 6.677 ms
Add Comment
Please, Sign In to add comment