Обсуждение: Bad query plan decision when using multiple column index - postgresqluses only first column then filters

Cosmin Prund <cprund@gmail.com> writes:
> explain analyze
>    select R, C, V from LBD
>    where Ver = 92 and Id in (10,11)

> Index Scan using "IX_LBD_Ver_Id" on "LBD"  (cost=0.56..2.37 rows=1
> width=13) (actual time=0.063..857.725 rows=2 loops=1)
>   Index Cond: ("Ver" = 92)
>   Filter: ("Id" = ANY ('{10,11}'::integer[]))
>   Rows Removed by Filter: 1869178
> Planning time: 0.170 ms
> Execution time: 857.767 ms

> The  IX_LBD_Ver_Id index is on two columns (Ver, Id) - it's not in "Ver"
> alone!

Seems like an odd choice of plan, then, but you haven't provided any
detail that would let anyone guess why it's not using the second index
column.  For starters it would be good to show the exact table and
index schema (eg via \d+ in psql).  Also, does explicitly ANALYZE'ing
the table change anything?

https://wiki.postgresql.org/wiki/Slow_Query_Questions

            regards, tom lane

Michael Lewis <mlewis@entrata.com> writes:
> Does the behavior change with different values of Ver column?

By and large, indxpath.c will just add all qual clauses that match
an index to the indexscan's conditions --- there's no attempt to
decide that some of them might not be worth it on cost grounds.
So I'd be pretty surprised if altering the Ver constant made any
difference.  My money is on there being some reason why the IN
clause doesn't match the index, perhaps a type mismatch.  Without
seeing the table schema, and the exact query, it's hard to say what
that reason is.  (I'll not insult your intelligence by saying how
I know that the OP didn't just copy-and-paste that query.)

            regards, tom lane

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

Cosmin Prund <cprund@gmail.com> writes:
> I know it's an odd choice of plan - that's why I'm here!

Indeed.  I cannot reproduce it here on 10.11:

regression=# create table bb(f1 smallint, f2 serial, primary key(f1,f2));
CREATE TABLE
regression=# explain select * from bb where f1 = 92 and f2 in (10,11);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Only Scan using bb_pkey on bb  (cost=0.15..8.34 rows=1 width=6)
   Index Cond: ((f1 = 92) AND (f2 = ANY ('{10,11}'::integer[])))
(2 rows)

As I said before, as long as it chooses an indexscan at all, I wouldn't
expect variation in what clauses it chooses to use with the index.
So I don't see why this trivial example doesn't replicate your result.

If you try exactly the above on your database, do you get my result,
or a plan more like yours?

I wonder if you have some extension installed that's causing the
operators to be interpreted differently.

BTW, why do you have two identical indexes on the table?

> Indexes:
>     "PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId",
> "LucrareBugetDateId")
>     "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree
> ("LucrareBugetVersiuneId", "LucrareBugetDateId")

That shouldn't be affecting this either, but it seems wasteful.

            regards, tom lane

Cosmin Prund <cprund@gmail.com> writes:
> Running the same query with a different "Ver" produces a proper plan.

Oh, that *is* interesting.

After studying the code a bit more I see why this is possible when I
originally thought not.  The case that you are interested in is one that
has special handling -- it's a "lower-order ScalarArrayOpExpr" in the
terms of the code.  This means that get_index_paths will actually produce
two index paths, one with the IN clause as an indexqual and one without,
because it expects that they have different sort behaviors [1].  So then
we do have a chance for a cost-based choice, making it possible for the
estimated selectivity of the higher-order clause to affect the outcome.

I'm still a bit surprised that it wouldn't choose the alternative with
the IN ... but if the estimated number of rows matching just the first
column is small enough, it might see the paths as having indistinguishable
costs, and then it's down to luck which it chooses.

> There are 25 valid values for "Ver" in this database. I ran the query for
> all of them. The only one miss-behaving is "92". I ran the query with
> random values for Ver (invalid values), the query plan always attempts to
> use the index using both values.
> I looked into "most_common_values" in pg_stats, this value (92) is not in
> that list.

Are the other 24 all in the list?

> Finally I ran "ANALYZE" again and now the problem went away. Running the
> query with Ver=92 uses the proper plan. I'm not happy with this - I know I
> haven't solved the problem (I've ran ANALYZE multiple times before).

Maybe increasing the stats target for the "Ver" column would help.  It
sounds like you want to get to a point where all the valid values are
given in the MCV list, so that the estimates for them will be accurate.

            regards, tom lane

[1] Right at the moment, it seems like that's wrong and we could just
generate one path.  Need to study this.