Re: Bad query plan decision when using multiple column index -postgresql uses only first column then filters

On Thu, 2020-01-16 at 19:18 +0200, Cosmin Prund wrote:
> Indexes:
>     "PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId", "LucrareBugetDateId")
>     "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree ("LucrareBugetVersiuneId",
"LucrareBugetDateId")
> Foreign-key constraints:
>     "FK_LucrareBugetDate_LucrareBugetVersiune_LucrareBugetVersiuneId" FOREIGN KEY ("LucrareBugetVersiuneId")
REFERENCES"LucrareBugetVersiune"("LucrareBugetVersiuneId") ON DELETE CASCADE
 
> 
> EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from "LucrareBugetDate" where
("LucrareBugetVersiuneId"= 92) and ("LucrareBugetDateId" in (10,11));
 
>                                                                                       QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" on "LucrareBugetDate"
(cost=0.56..2.37rows=1 width=13) (actual time=0.096..978.398 rows=2 loops=1)
 
>    Index Cond: ("LucrareBugetVersiuneId" = 92)
>    Filter: ("LucrareBugetDateId" = ANY ('{10,11}'::integer[]))
>    Rows Removed by Filter: 1869178
>    Buffers: shared hit=161178
>  Planning time: 0.699 ms
>  Execution time: 978.433 ms

Well, what should the poor thing do?
There is no index on "LucrareBugetDateId".

Rather, you have two indexes on ("LucrareBugetVersiuneId", "LucrareBugetDateId"),
one of which should be dropped.

Try with an index on "LucrareBugetDateId".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com