On Fri, 4 Jan 2019 at 02:20, Abadie Lana <lana.aba...@iter.org> wrote: > > From: David Rowley <david.row...@2ndquadrant.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