Postgres does not use index with IN

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Postgres does not use index with IN

Дмитрий Вилькер
Hello.
 
As example:
 
DROP TABLE IF EXISTS parent CASCADE;
DROP TABLE IF EXISTS child CASCADE;
CREATE TABLE parent (id BIGSERIAL PRIMARY KEY);
CREATE TABLE child (id BIGSERIAL PRIMARY KEY, parent_id BIGINT, dummy INT);
CREATE INDEX c2p ON child USING btree (parent_id);
INSERT INTO parent SELECT FROM generate_series(1, 5000);
INSERT INTO child (parent_id) SELECT 1 + FLOOR(random()*5000) FROM generate_series(1, 100000);
VACUUM ANALYZE parent;
VACUUM ANALYZE child;
 
 
-- Index c2p - USED
EXPLAIN ANALYZE UPDATE child ch
SET dummy = 1
WHERE ch.parent_id IN (1, 2, 3);
 
-- Index c2p - NOT USED (Seq Scan on child ch). Why?
EXPLAIN ANALYZE UPDATE child ch
SET dummy = 1
WHERE ch.parent_id IN (SELECT id FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */);
 
-- Index c2p - USED
EXPLAIN ANALYZE UPDATE child ch
SET dummy = 1
WHERE ch.parent_id = ANY ( (SELECT array_agg(id) FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */)::BIGINT[] );
 
 
Results:
 
**Query #1**
 
    EXPLAIN ANALYZE UPDATE child ch
    SET dummy = 1
    WHERE ch.parent_id IN (1, 2, 3);
 
| QUERY PLAN                                                                                                           |
| -------------------------------------------------------------------------------------------------------------------- |
| Update on child ch  (cost=13.34..184.10 rows=59 width=26) (actual time=0.988..0.988 rows=0 loops=1)                  |
|   ->  Bitmap Heap Scan on child ch  (cost=13.34..184.10 rows=59 width=26) (actual time=0.043..0.314 rows=63 loops=1) |
|         Recheck Cond: (parent_id = ANY ('{1,2,3}'::bigint[]))                                                        |
|         Heap Blocks: exact=59                                                                                        |
|         ->  Bitmap Index Scan on c2p  (cost=0.00..13.33 rows=59 width=0) (actual time=0.025..0.025 rows=63 loops=1)  |
|               Index Cond: (parent_id = ANY ('{1,2,3}'::bigint[]))                                                    |
| Planning Time: 0.381 ms                                                                                              |
| Execution Time: 1.030 ms                                                                                             |
 
 
**Query #2**
 
    EXPLAIN ANALYZE UPDATE child ch
    SET dummy = 1
    WHERE ch.parent_id IN (SELECT id FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */);
 
| QUERY PLAN                                                                                                                  |
| --------------------------------------------------------------------------------------------------------------------------- |
| Update on child ch  (cost=105.19..1908.91 rows=1500 width=32) (actual time=35.469..35.469 rows=0 loops=1)                   |
|   ->  Hash Join  (cost=105.19..1908.91 rows=1500 width=32) (actual time=35.102..35.121 rows=42 loops=1)                     |
|         Hash Cond: (ch.parent_id = parent.id)                                                                               |
|         ->  Seq Scan on child ch  (cost=0.00..1541.00 rows=100000 width=22) (actual time=0.013..28.007 rows=100000 loops=1) |
|         ->  Hash  (cost=104.25..104.25 rows=75 width=14) (actual time=0.799..0.799 rows=2 loops=1)                          |
|               Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                  |
|               ->  Seq Scan on parent  (cost=0.00..104.25 rows=75 width=14) (actual time=0.009..0.793 rows=2 loops=1)        |
|                     Filter: ((id + 1) = ANY ('{1,2,3}'::bigint[]))                                                          |
|                     Rows Removed by Filter: 4998                                                                            |
| Planning Time: 0.400 ms                                                                                                     |
| Execution Time: 35.501 ms                                                                                                   |
 
 
**Query #3**
 
    EXPLAIN ANALYZE UPDATE child ch
    SET dummy = 1
    WHERE ch.parent_id = ANY ( (SELECT array_agg(id) FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */)::BIGINT[] );
 
| QUERY PLAN                                                                                                            |
| --------------------------------------------------------------------------------------------------------------------- |
| Update on child ch  (cost=148.93..546.32 rows=200 width=26) (actual time=0.561..0.561 rows=0 loops=1)                 |
|   InitPlan 1 (returns $0)                                                                                             |
|     ->  Aggregate  (cost=104.44..104.45 rows=1 width=32) (actual time=0.461..0.461 rows=1 loops=1)                    |
|           ->  Seq Scan on parent  (cost=0.00..104.25 rows=75 width=8) (actual time=0.004..0.458 rows=2 loops=1)       |
|                 Filter: ((id + 1) = ANY ('{1,2,3}'::bigint[]))                                                        |
|                 Rows Removed by Filter: 4998                                                                          |
|   ->  Bitmap Heap Scan on child ch  (cost=44.47..441.87 rows=200 width=26) (actual time=0.506..0.513 rows=42 loops=1) |
|         Recheck Cond: (parent_id = ANY ($0))                                                                          |
|         Heap Blocks: exact=2                                                                                          |
|         ->  Bitmap Index Scan on c2p  (cost=0.00..44.42 rows=200 width=0) (actual time=0.475..0.476 rows=94 loops=1)  |
|               Index Cond: (parent_id = ANY ($0))                                                                      |
| Planning Time: 0.107 ms                                                                                               |
| Execution Time: 0.588 ms                                                                                              |