Re: select query does not pick up the right index

On Fri, 4 Jan 2019 at 02:20, Abadie Lana <Lana(dot)Abadie(at)iter(dot)org> wrote:
> > From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> > Sent: 03 January 2019 14:01
> Right, so you need to check your indexes on sample_ctrl_year and sample_buil_year. You need an index on (channel_id, smpl_time) on those.

> These indexes exist already

That's interesting. The \d output indicates that the indexes are not
INVALID, so it's not all that obvious why the planner would choose a
lesser index to provide the required rows. One thought is that the
more suitable index is very bloated. This would increase the
estimated cost of scanning the index and reduce the chances of the
index being selected by the query planner.

If you execute:

select indrelid::regclass as table_name, indexrelid::Regclass as
index_name,pg_size_pretty(pg_relation_size(indrelid))
table_size,pg_size_pretty(pg_relation_size(indexrelid)) index_size
from pg_index
where indrelid in('sample_ctrl_year'::regclass, 'sample_buil_year'::regclass)
order by indrelid::regclass::name, indexrelid::regclass::name;

This should show you the size of the tables and indexes in question.
If the sample_time_cy_idx and sample_time_by_idx indexes are very
large when compared with the size of their table, then it is likely
worth building a new index for these then dropping the old index then
retrying the re-written version of the query. If this is a live
system then you can build the new indexes by using the CREATE INDEX
CONCURRENTLY command. This will allow other DML operations to work
without being blocked. The old indexes can then be dropped with DROP
INDEX CONCURRENTLY.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services