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

Running ANALYZE doesn't change a thing. REINDEXING doesn't change a thing. I know it's an odd choice of plan - that's why I'm here!

I thought I'd just post what felt relevant, hoping it's not something out of the ordinary and I'm just missing something obvious.

Here's lots of data:

===========================================================================

PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

===========================================================================

 \d "LucrareBugetDate"
                                                       Table "public.LucrareBugetDate"
           Column           |         Type          | Collation | Nullable |                             Default
----------------------------+-----------------------+-----------+----------+------------------------------------------------------------------
 OrdonatorPrincipalId       | uuid                  |           |          |
 UnitateSubordonataId       | uuid                  |           |          |
 CentralizatorSelectiv      | text                  |           |          |
 IdRand                     | character varying(32) |           |          |
 IdColoana                  | character varying(32) |           |          |
 ClasEc                     | character varying(32) |           |          |
 CodSector                  | character varying(4)  |           |          |
 CodSursa                   | character varying(4)  |           |          |
 Paragraf                   | character varying(16) |           |          |
 Venit                      | character varying(16) |           |          |
 FelValoare                 | integer               |           | not null |
 Valoare                    | numeric               |           | not null |
 RangOperator               | integer               |           | not null |
 OrdineCalcul               | integer               |           | not null |
 ConflictFormuleAlternative | boolean               |           | not null | false
 Sectiune                   | integer               |           |          |
 RefColoana                 | text                  |           |          |
 RefDocument                | text                  |           |          |
 RefLinie                   | text                  |           |          |
 SeqModificare              | integer               |           | not null | 0
 LucrareBugetDateId         | integer               |           | not null | nextval('"LucrareBugetDate_LucrareBugetDateIdV2_seq"'::regclass)
 LucrareBugetVersiuneId     | smallint              |           | not null |
 CentralizatorSelectivId    | uuid                  |           |          |
 Stil                       | text                  |           |          |
 ValoareArhivata            | boolean               |           |          |
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

===========================================================================

SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='LucrareBugetDate';
     relname      | relpages |  reltuples  | relallvisible | relkind | relnatts | relhassubclass |   reloptions    | pg_table_size
------------------+----------+-------------+---------------+---------+----------+----------------+-----------------+---------------
 LucrareBugetDate |  2659660 | 4.17124e+07 |        671510 | r       |       25 | f              | {fillfactor=50} |   21793775616
(1 row)

===========================================================================

  ===========================================================================  

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.37 rows=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

===========================================================================

Was this query always slow, or has it gotten slower over time? If the plan/execution of the query used to be different, do you have copies of those query plans? Has anything changed in your database other than the accumulation of data? 

The query is usually instantaneous. 

Here's the same query ran o a different server running the same database with comparable data (COLD server, frist run! The second run has execution time = 0.040ms):

  EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from "LucrareBugetDate" where ("LucrareBugetVersiuneId" = 92) and ("LucrareBugetDateId" in (10,11));  

Index Scan using "PK_LucrareBugetDate" on "LucrareBugetDate"  (cost=0.56..4.85 rows=2 width=13) (actual time=22.922..23.123 rows=2 loops=1)

  Index Cond: (("LucrareBugetVersiuneId" = 92) AND ("LucrareBugetDateId" = ANY ('{10,11}'::integer[])))

  Buffers: shared hit=12 read=4

Planning time: 66.743 ms

Execution time: 23.190 ms

I doubt it's hardware related.

SELECT * FROM pg_stat_user_tables WHERE relname='table_name'; 

  relid | schemaname |     relname      | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |          last_vacuum          | last_autovacuum |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count

SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='LucrareBugetDateId' AND tablename='LucrareBugetDate' ORDER BY 1 DESC;

  frac_mcv  |    tablename     |      attname       | inherited | null_frac | n_distinct  | n_mcv | n_hist | correlation
------------+------------------+--------------------+-----------+-----------+-------------+-------+--------+-------------
 0.00666667 | LucrareBugetDate | LucrareBugetDateId | f         |         0 | 2.02985e+06 |   100 |    101 |   0.0631249

  =========================================================================== 

 SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='LucrareBugetVersiuneId' AND tablename='LucrareBugetDate' ORDER BY 1 DESC;

   ===========================================================================  

I think I went through most of https://wiki.postgresql.org/wiki/SlowQueryQuestions

I can provide more information if helpful.