Re: BUG #15227: Planner often ignores covering indexes (with include clause) - Mailing list pgsql-bugs

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I encountered different problem cases where specially changed indexes with
> include clause where not used:
> ...
> Then I got a plan change from     
> Index Scan using projects_active_pr_season_pr_cli_code_pr_name_key on
> projects (cost=0.28..106.74 rows=1,990 width=53) (actual time=0.007..0.596
> rows=1,990 loops=1)
>     Index Cond: (pr_season = 26)
> to  
> Seq Scan on public.projects (cost=0.00..114.80 rows=1,990 width=53) (actual
> time=0.036..0.451 rows=1,990 loops=1)
>     Output: projects.id_pr, projects.pr_last, projects.pr_style,
> projects.pr_photo_default, projects.pr_cli_code, projects.pr_season
>     Filter: (projects.pr_season = 26)
>     Rows Removed by Filter: 2154

While it's certainly possible that there are costing bugs in the new
covering-index code, this example doesn't seem to prove that.  The row
counts show that this query is selecting close to 50% of the table,
which is a situation in which an indexscan is usually a loser compared
to seqscan-and-filter anyway.  Indeed, the seqscan is *faster* than the
indexscan on the non-PK index according to your results above (0.451 ms
versus 0.596 ms).  The covering index would be substantially bigger than
the non-PK index, hence even slower to scan, so I think the planner made
the right choice.

This conclusion might change if you had an index-only scan, but probably
not by much, especially since the table is so small.  (The mere fact that
the index is covering doesn't guarantee an IOS; you also need a table
that is mostly all-visible.  I speculate that maybe you didn't vacuum
the test table, or modified a lot of it since the last vacuum.)

In general, I'm suspicious of the idea of putting the entire table
into a covering index as you've done here.  The covering index will
almost certainly be significantly larger than the table itself ---
remember the old rule of thumb that b-trees tend to have about 1/3rd
empty space.  So a query that fetches much or all of the table is
still going to be better off with a seqscan, as that will be less I/O
and it'll use more-sequential disk accesses.  Creating an index like
this also disables HOT updates altogether, which may be a significant
penalty depending on what your update patterns are.

> When the other index (projects_active_pr_season_pr_cli_code_pr_name_key) is
> active, it is choosen over the changed primary key for index scan.

Probably because it's smaller.  Again, this suggests that you're not in
a situation where IOS is possible, else perhaps the covering index would
have an advantage from that.

> 2. Choosing between comparable indexes

> This gives the same plan as in 10.4

> Index Scan using
> models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key on
> public.models (cost=0.28..73.01 rows=990 width=32) (actual time=0.006..0.222
> rows=990 loops=1) 

> When the second index
> models_active_am_season_am_id_pr_am_style_ref_am_clis_sub_c_key is
> disabled,
> The plan changes to the Index Only scan using the new Index as intended:

> Index Only Scan using ukp_models_season_id on public.models
> (cost=0.28..74.01 rows=990 width=32) (actual time=0.011..0.270 rows=990
> loops=1)
>     Output: models.am_season, models.id_am, models.am_fac_code,
> models.am_id_pr, models.am_clis_sub_code, models.am_fac_id_cu,
> models.am_our_id_cu, models.am_style_ref
>     Index Cond: (models.am_season = 26)
>     Heap Fetches: 990

This example isn't exactly proving that the planner did the wrong
thing, either.  The estimated costs and actual times are close
enough together that I'd freely concede that maybe it was luck that
the planner preferred the in-fact-faster plan; but it did.

            regards, tom lane