RE: select query does not pick up the right index - Mailing list pgsql-performance

-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com> 
Sent: 03 January 2019 22:42
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index

On Fri, 4 Jan 2019 at 02:20, Abadie Lana <Lana.Abadie@iter.org> wrote:
> > From: David Rowley <david.rowley@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
plannerwould 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
selectedby 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,
thenit is likely worth building a new index for these then dropping the old index then retrying the re-written version
ofthe 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
DROPINDEX 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
forbuil 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
queryit will take Execution time: 247503.006 ms ( I can see that postgres process is in state D and low CPU...,using
iotopI 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).