From: | benj(dot)dev(at)laposte(dot)net |
---|---|
To: | Pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Planner choose to use big index instead of smaller one |
Date: | 2022-11-23 10:59:36 |
Message-ID: | 846261823.2789764.1669201176926@wlpnf0212 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have a table tpoint near that 820Mo with 700K lignes
I have created 2 index on this table :
CREATE INDEX idx_small_index ON public.tpoint USING btree (match_id);
-- Size : 4560 kB
CREATE INDEX idx_big_index ON public.tpoint USING btree (version, match_id, playnum, code_action, num_balle );
-- Size : 34 MB
If I execute this request
EXPLAIN(analyse, buffers)
SELECT count(*) FROM tpoint
WHERE match_id = 'SM001'
The query planner use the idx_small_index as expected
-> Index Only Scan using idx_small_index on tpoint (cost=0.42..507.10 rows=624 width=0) (actual time=0.025..0.160 rows=1017 loops=1)
Index Cond: (match_id = 'SM001'::bpchar)
Heap Fetches: 199
Buffers: shared hit=45
But if I execute this other request
EXPLAIN(analyse, buffers)
SELECT count(*) FROM tpoint
WHERE match_id LIKE 'SM001%'
The query planner use idx_big_index
-> Index Only Scan using idx_big_index on tpoint (cost=0.42..73940.37 rows=5191 width=0) (actual time=111.014..143.379 rows=1017 loops=1)
Filter: (match_id ~~ 'SM001%'::text)
Rows Removed by Filter: 636819
Heap Fetches: 132426
Buffers: shared hit=473963
I really don't understand why the planner prefers to use the "big index" instead of the "small" one that containt all needed element to do an INDEX ONLY SCAN.
If I deactivate the "big index"
UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'idx_big_index'::regclass
I can see that for a first explain :
-> Index Only Scan using idx_small_index on tpoint (cost=0.42..92107.62 rows=5191 width=0) (actual time=59.980..78.683 rows=1017 loops=1)
Filter: (match_id ~~ 'SM001%'::text)
Rows Removed by Filter: 636819
Heap Fetches: 132426
Buffers: shared hit=27668 read=564
or that for a second explain (No read):
-> Index Only Scan using idx_small_index on tpoint (cost=0.42..92107.62 rows=5191 width=0) (actual time=64.012..81.122 rows=1017 loops=1)
Filter: (match_id ~~ 'SM001%'::text)
Rows Removed by Filter: 636819
Heap Fetches: 132426
Buffers: shared hit=28232
Tests realized on a docker postgres
version = PostgreSQL 15.0 (Debian 15.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
SET max_parallel_workers_per_gather TO 0
Thanks for any explanation
regards
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2022-11-23 11:56:36 | Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline... |
Previous Message | Nikolas Hanry | 2022-11-23 10:23:04 | Fwd: Change the auth. postgresql and GIS |