-----Original Message-----
From: David Rowley <david.row...@2ndquadrant.com>
Sent: 03 January 2019 22:42
To: Abadie Lana <lana.aba...@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.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
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).
