-----Original Message-----
From: David Rowley <david.rowley@xxxxxxxxxxxxxxx>
Sent: 03 January 2019 22:42
To: Abadie Lana <Lana.Abadie@xxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Subject: Re: select query does not pick up the right index
On Fri, 4 Jan 2019 at 02:20, Abadie Lana <Lana.Abadie@xxxxxxxx> wrote:
> > From: David Rowley <david.rowley@xxxxxxxxxxxxxxx>
> > 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
Here the result...For me it does not sound that it is bloated...Also still a mystery why wrong indexes are picked up for buil and ctrl and not for util...
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,'sample_util_year'::regclass) order by indrelid::regclass::name, indexrelid::regclass::name;
table_name | index_name | table_size | index_size
------------------+---------------------+------------+------------
sample_buil_year | sample_time_by_idx | 4492 MB | 1522 MB
sample_buil_year | sample_time_yb1_idx | 4492 MB | 1522 MB
sample_buil_year | smpl_time_bx2_idx | 4492 MB | 1084 MB
sample_ctrl_year | sample_time_cy_idx | 7065 MB | 2394 MB
sample_ctrl_year | sample_time_yc1_idx | 7065 MB | 2394 MB
sample_ctrl_year | smpl_time_cmx2_idx | 7065 MB | 1705 MB
sample_util_year | sample_time_uy_idx | 7140 MB | 2426 MB
sample_util_year | sample_time_yu1_idx | 7140 MB | 2426 MB
sample_util_year | smpl_time_ux2_idx | 7140 MB | 1727 MB
(9 rows)
I have recreated the indexes for sample_ctrl_year and sample_buil_year and same index size.
I rerun the query... and still the same plan execution as previously sent....
Thanks for your support...One thing I spot is the I/O on this machine is rather slow... the very first time I run this query it will take Execution time: 247503.006 ms ( I can see that postgres process is in state D and low CPU...,using iotop I can see I/O read speed cannot go beyond 20MB/sec. The second time I run the query, the CPU goes up to 100%, no D state).
