pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect query does not pick up the right index
21+ messages / 3 participants
[nested] [flat]
* select query does not pick up the right index
@ 2019-01-02 16:28 Abadie Lana <Lana.Abadie@iter.org>
2019-01-02 16:45 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 2 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-02 16:28 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi all
I would appreciate any hints as this problem looks to me rather strange...I tried to google it but in vain.
select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
takes 20mn to execute because it picks up the wrong index...see explain analyse below. I would expect this query to use the (channel_id,smpl_time) but it uses the smpl_time index.
I have run analyse on the sample table. I have set default_statistics_target = 1000
When I removed this index, then the query goes down to a few seconds...
Any ideas, why the planner is not taking the right index?
Postgresql server is 10.5.1 running on RHEL 7.4
More details about the table and explain...
Thanks for your help
Lana
\d+ sample
Table "public.sample"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_1_idx" btree (channel_id, smpl_time)
"sample_time_all_idx" btree (smpl_time, channel_id)
"smpl_time_qa_idx" btree (smpl_time)
Child tables: sample_buil,
sample_ctrl,
sample_util
\d+ sample_buil
Table "public.sample_buil"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_b1_idx" btree (smpl_time, channel_id)
"sample_time_b_idx" btree (channel_id, smpl_time)
"smpl_time_bx0_idx" btree (smpl_time)
Inherits: sample
Child tables: sample_buil_month,
sample_buil_year
\d+ sample_buil_month
Table "public.sample_buil_month"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_bm_idx" btree (channel_id, smpl_time)
"sample_time_mb1_idx" btree (smpl_time, channel_id)
"smpl_time_bx1_idx" btree (smpl_time)
Check constraints:
"sample_buil_month_smpl_time_check" CHECK (smpl_time >= (now() - '32 days'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_buil
css_archive_3_0_0=# explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_ val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_i d and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
-------------
Gather (cost=1004.71..125606.08 rows=5 width=150) (actual time=38737.443..1220277.244 rows
=3 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Limit (cost=4.71..124605.58 rows=5 width=150) (actual time=38731.488..1220117.046 ro
ws=3 loops=1)
-> Nested Loop (cost=4.71..240130785.25 rows=9636 width=150) (actual time=38731.4
86..1220117.040 rows=3 loops=1)
Join Filter: (c.channel_id = t.channel_id)
Rows Removed by Join Filter: 322099471
-> Merge Append (cost=4.71..235298377.47 rows=322099464 width=125) (actual
time=0.681..943623.198 rows=322099474 loops=1)
Sort Key: c.smpl_time DESC
-> Index Scan Backward using smpl_time_qa_idx on sample c (cost=0.12.
.8.14 rows=1 width=334) (actual time=0.010..0.010 rows=0 loops=1)
-> Index Scan Backward using smpl_time_bx0_idx on sample_buil c_1 (co
st=0.42..3543026.23 rows=1033169 width=328) (actual time=0.122..723.286 rows=1033169 loops=1
)
-> Index Scan Backward using smpl_time_cmx0_idx on sample_ctrl c_2 (c
ost=0.42..2891856.90 rows=942520 width=328) (actual time=0.069..712.386 rows=942520 loops=1)
-> Index Scan Backward using smpl_time_ux0_idx on sample_util c_3 (co
st=0.43..11310958.12 rows=5282177 width=328) (actual time=0.066..3688.980 rows=5282177 loops
=1)
-> Index Scan Backward using smpl_time_bx1_idx on sample_buil_month c_
4 (cost=0.43..49358435.15 rows=14768705 width=82) (actual time=0.070..9341.396 rows=1476870
5 loops=1)
-> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5
(cost=0.56..1897430.89 rows=50597832 width=328) (actual time=0.068..139840.439 rows=505978
34 loops=1)
-> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c
_6 (cost=0.44..55253292.21 rows=18277124 width=85) (actual time=0.061..14610.389 rows=18277
123 loops=1)
-> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_
7 (cost=0.57..2987358.31 rows=79579072 width=76) (actual time=0.067..286316.865 rows=795790
75 loops=1)
-> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_
8 (cost=0.57..98830163.45 rows=70980976 width=82) (actual time=0.071..60766.643 rows=709809
80 loops=1)
-> Index Scan Backward using smpl_time_ux2_idx on sample_util_year c_9
(cost=0.57..3070642.94 rows=80637888 width=83) (actual time=0.069..307091.673 rows=8063789
1 loops=1)
-> Materialize (cost=0.00..915.83 rows=1 width=41) (actual time=0.000..0.00
0 rows=1 loops=322099474)
-> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual
time=4.683..7.885 rows=1 loops=1)
Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Rows Removed by Filter: 33425
Planning time: 31.392 ms
Execution time: 1220277.424 ms
(26 rows)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-02 16:45 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-03 12:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
1 sibling, 1 reply; 21+ messages in thread
From: Justin Pryzby @ 2019-01-02 16:45 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Wed, Jan 02, 2019 at 04:28:41PM +0000, Abadie Lana wrote:
> css_archive_3_0_0=# explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_ val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_i d and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------
> -------------
> Gather (cost=1004.71..125606.08 rows=5 width=150) (actual time=38737.443..1220277.244 rows
> =3 loops=1)
> Workers Planned: 1
> Workers Launched: 1
> Single Copy: true
Do you have force_parallel_mode set ?
http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-02 16:45 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
@ 2019-01-03 12:58 ` Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 0 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-03 12:58 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 02 January 2019 17:45
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 Wed, Jan 02, 2019 at 04:28:41PM +0000, Abadie Lana wrote:
> css_archive_3_0_0=# explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_ val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_i d and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------
> -------------
> Gather (cost=1004.71..125606.08 rows=5 width=150) (actual time=38737.443..1220277.244 rows
> =3 loops=1)
> Workers Planned: 1
> Workers Launched: 1
> Single Copy: true
Do you have force_parallel_mode set ?
http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
Hi Justin
Indeed force_parallel_mode was set to on. Even after disabling it, same issue...
cheers
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 00:15 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
1 sibling, 1 reply; 21+ messages in thread
From: David Rowley @ 2019-01-03 00:15 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, 3 Jan 2019 at 05:28, Abadie Lana <Lana.Abadie@iter.org> wrote:
> I would appreciate any hints as this problem looks to me rather strange…I tried to google it but in vain.
>
> select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
>
> takes 20mn to execute because it picks up the wrong index…see explain analyse below. I would expect this query to use the (channel_id,smpl_time) but it uses the smpl_time index.
[...]
> Any ideas, why the planner is not taking the right index?
The planner assumes that the required channel values are evenly
distributed through the scan of the index on smpl_time. If your
required 5 rows were found quickly (i.e channels with recent sample
values), then the plan would have worked out well. It looks like
'BUIL-B36-VA-RT-RT1:CL0001-2-ABW' is probably a channel which has some
very old sample values. I can see that from "Rows Removed by Join
Filter: 322099471", meaning that on backwards scanning the smpl_time
index, that many rows were found not to match the channel you
requested.
The planner, by default only has statistics to say how common each
channel is in the sample table. I think in this case since the planner
has no knowledge of which channel_id it will be searching for (that's
only determined during execution), then I suppose it must be using the
n_distinct of the sample.channel_id table. It would be interesting to
know how far off the n_distinct estimation is. You can find out with:
select stadistinct from pg_statistic where starelid='sample'::regclass
and staattnum = 1;
select count(*) from (select distinct channel_id from sample) s; --
this may take a while to run...
If the stadistinct estimate is far out from the reality, then you
could consider setting this manually with:
alter table sample alter column channel_id set (n_distinct = <actual
value here>);
but keep in mind, that as the table evolves, whatever you set there
could become outdated.
Another method to fix you could try would be to coax the planner into
doing something different would be to give it a better index to work
with.
create index on channel(name, channel_id);
You didn't show us the details from the channel table, but if there's
not an index like this then this might reduce the cost of a Merge
Join, but since the order rows output from that join would be in
channel_id order, a Sort would be required, which would require
joining all matching rows, not just the first 5 matches. Depending on
how many rows actually match will determine if that's faster or not.
If you don't have luck with either of the above then, one other thing
you could try would be to disallow the planner from using the
smpl_time index by changing the order by to "ORDER BY c.smpl_time +
INTERVAL '0 sec'; that's a bit of a hack, but we don't have anything
we officially call "query hints" in PostgreSQL, so often we're left to
solve issues like this with ugly tricks like that.
Also, going by:
> -> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual time=4.683..7.885 rows=1 loops=1)
perhaps "name" is unique on the channel table? (I doubt there's an
index/constraint to back that up, however, since such an index would
have likely been used here instead of the Seq Scan)
If so, and you can add a constraint to back that up, you might be
able to reform the query to be:
select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',
c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val
from sample c
WHERE c.channel_id = (SELECT channel_id FROM channel WHERE
name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW')
order by c.smpl_time desc limit 5;
If you can do that then it's highly likely to be *very* fast to
execute since I see there's an index on (channel_id, smpl_time) on
each of the inherited tables.
(If our planner was smarter then in the presence of the correct unique
index, we could have rewritten the query as such automatically.... but
it's not / we don't. I believe I've mentioned about improving this
somewhere in the distant past of the -hackers mailing list, although I
can't find it right now. I recall naming the idea "scalar value lookup
joins", but development didn't get much beyond thinking of that name)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
@ 2019-01-03 12:57 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-03 12:57 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 03 January 2019 01:16
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 Thu, 3 Jan 2019 at 05:28, Abadie Lana <Lana.Abadie@iter.org> wrote:
> I would appreciate any hints as this problem looks to me rather strange…I tried to google it but in vain.
>
> select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
>
> takes 20mn to execute because it picks up the wrong index…see explain analyse below. I would expect this query to use the (channel_id,smpl_time) but it uses the smpl_time index.
[...]
> Any ideas, why the planner is not taking the right index?
The planner assumes that the required channel values are evenly distributed through the scan of the index on smpl_time. If your required 5 rows were found quickly (i.e channels with recent sample values), then the plan would have worked out well. It looks like 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW' is probably a channel which has some very old sample values. I can see that from "Rows Removed by Join
Filter: 322099471", meaning that on backwards scanning the smpl_time index, that many rows were found not to match the channel you requested.
The planner, by default only has statistics to say how common each channel is in the sample table. I think in this case since the planner has no knowledge of which channel_id it will be searching for (that's only determined during execution), then I suppose it must be using the n_distinct of the sample.channel_id table. It would be interesting to know how far off the n_distinct estimation is. You can find out with:
select stadistinct from pg_statistic where starelid='sample'::regclass and staattnum = 1; select count(*) from (select distinct channel_id from sample) s; -- this may take a while to run...
If the stadistinct estimate is far out from the reality, then you could consider setting this manually with:
alter table sample alter column channel_id set (n_distinct = <actual value here>);
but keep in mind, that as the table evolves, whatever you set there could become outdated.
Another method to fix you could try would be to coax the planner into doing something different would be to give it a better index to work with.
create index on channel(name, channel_id);
You didn't show us the details from the channel table, but if there's not an index like this then this might reduce the cost of a Merge Join, but since the order rows output from that join would be in channel_id order, a Sort would be required, which would require joining all matching rows, not just the first 5 matches. Depending on how many rows actually match will determine if that's faster or not.
If you don't have luck with either of the above then, one other thing you could try would be to disallow the planner from using the smpl_time index by changing the order by to "ORDER BY c.smpl_time +
INTERVAL '0 sec'; that's a bit of a hack, but we don't have anything
we officially call "query hints" in PostgreSQL, so often we're left to solve issues like this with ugly tricks like that.
Also, going by:
> -> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual
> -> time=4.683..7.885 rows=1 loops=1)
perhaps "name" is unique on the channel table? (I doubt there's an index/constraint to back that up, however, since such an index would have likely been used here instead of the Seq Scan)
If so, and you can add a constraint to back that up, you might be able to reform the query to be:
select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',
c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val
from sample c
WHERE c.channel_id = (SELECT channel_id FROM channel WHERE
name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW')
order by c.smpl_time desc limit 5;
If you can do that then it's highly likely to be *very* fast to execute since I see there's an index on (channel_id, smpl_time) on each of the inherited tables.
(If our planner was smarter then in the presence of the correct unique index, we could have rewritten the query as such automatically.... but it's not / we don't. I believe I've mentioned about improving this somewhere in the distant past of the -hackers mailing list, although I can't find it right now. I recall naming the idea "scalar value lookup joins", but development didn't get much beyond thinking of that name)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Hi David
Thanks for your tips. So answers to your questions/comments
1) About n_distinct
first query returns 2136, second query returns 33425. So it seems that there is some discrepancies...Also the sample table is very big...roughly 322099474 rows.
I did the alter statement but without success. Still long execution time with wrong index
2) index on channel(name,channel_id)
There was no indexes on channel. So I created it. Same execution time, still wrong index used regardless of the n_distinct values
3)The "trick" (+ interval '0s') did the job. The index on channel_id, smpl_time is used. Query time can vary between a few ms to 25 sec
4) name is unique, constraint and index created. Right index is picked up and query time is rather constant there 40sec.
A few comments :
- I have disabled force_parallel_mode when running all the tests.
- The difference between the two plans is in the case of query with the trick, the planner is using a bitmap index scan, in the second one it uses index scan backward.
- when I execute the initial query, there is a big read access on disk almost 17.7 GB...whereas the total size of the smpl_time index is roughly 7GB...Could it be a wrong configuration on my side?
During the tests, no insert/delete/or update was performed...only my select queries...
Main parameters : effective_cache_size : 4GB, shared_buffers 4GB, work_mem 4MB
Thanks a lot !
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 13:01 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: David Rowley @ 2019-01-03 13:01 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Fri, 4 Jan 2019 at 01:57, Abadie Lana <Lana.Abadie@iter.org> wrote:
> 4) name is unique, constraint and index created. Right index is picked up and query time is rather constant there 40sec.
That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
@ 2019-01-03 13:13 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-03 13:13 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 03 January 2019 14:01
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 01:57, Abadie Lana <Lana.Abadie@iter.org> wrote:
> 4) name is unique, constraint and index created. Right index is picked up and query time is rather constant there 40sec.
That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
explain (analyze,buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=13.40..20.22 rows=5 width=233) (actual time=41023.057..41027.412 rows=3 loops=1)
Buffers: shared hit=75782139 read=1834969
InitPlan 1 (returns $0)
-> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=2.442..2.443 rows=1 loops=
1)
Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Buffers: shared read=4
-> Result (cost=4.96..8344478.65 rows=6117323 width=233) (actual time=41023.055..41027.408 rows=3 loops=1)
Buffers: shared hit=75782139 read=1834969
-> Merge Append (cost=4.96..8283305.42 rows=6117323 width=201) (actual time=41023.054..41027.404 rows=3 loops=1)
Sort Key: c.smpl_time DESC
Buffers: shared hit=75782139 read=1834969
-> Index Scan Backward using smpl_time_qa_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual time=0
.008..0.009 rows=0 loops=1)
Filter: (channel_id = $0)
Buffers: shared hit=1
-> Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..22318.03 rows=6300 width=320)
(actual time=2.478..2.478 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=7
-> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..116482.81 rows=33661 width=32
0) (actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=3
-> Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..35366.72 rows=9483 width=320)
(actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=3
-> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..60293.88 rows=15711 wi
dth=74) (actual time=5.499..9.847 rows=3 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=8
-> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364
width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
Filter: (channel_id = $0)
Rows Removed by Filter: 50597834
Buffers: shared hit=25913147 read=713221
-> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562
width=77) (actual time=0.048..0.048 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=4
-> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186
width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
Filter: (channel_id = $0)
Rows Removed by Filter: 79579075
Buffers: shared hit=49868991 read=1121715
-> Index Scan Backward using sample_time_um_idx on sample_util_month c_8 (cost=0.57..360454.53 rows=97101 w
idth=74) (actual time=0.058..0.059 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=4
-> Index Scan Backward using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..498663.22 rows=160954 w
idth=75) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=4
Planning time: 0.782 ms
Execution time: 41027.570 ms
(45 rows)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 13:17 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: David Rowley @ 2019-01-03 13:17 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
> From: David Rowley <david.rowley@2ndquadrant.com>
> Sent: 03 January 2019 14:01
> That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
>
> explain (analyze,buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
> -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364
> width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 50597834
> Buffers: shared hit=25913147 read=713221
> -> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562
> width=77) (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared read=4
> -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186
> width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 79579075
> Buffers: shared hit=49868991 read=1121715
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.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
@ 2019-01-03 13:20 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 14:34 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 21:42 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 3 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-03 13:20 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 03 January 2019 14:18
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
> From: David Rowley <david.rowley@2ndquadrant.com>
> Sent: 03 January 2019 14:01
> That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
>
> explain (analyze,buffers) select
> 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c
> .num_val,c.str_val,c.datatype,c.array_val from sample c WHERE
> c.channel_id = (SELECT channel_id FROM channel WHERE
> name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc
> limit 5;
> -> Index Scan Backward using smpl_time_bx2_idx on
> sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364
> width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 50597834
> Buffers: shared hit=25913147 read=713221
> -> Index Scan Backward using sample_time_cm_idx on
> sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562
> width=77) (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared read=4
> -> Index Scan Backward using smpl_time_cmx2_idx on
> sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186
> width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 79579075
> Buffers: shared hit=49868991 read=1121715
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
\d sample_ctrl_year
Table "public.sample_ctrl_year"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-------------
channel_id | bigint | | not null |
smpl_time | timestamp without time zone | | not null |
nanosecs | bigint | | not null |
severity_id | bigint | | not null |
status_id | bigint | | not null |
num_val | integer | | |
float_val | double precision | | |
str_val | character varying(120) | | |
datatype | character(1) | | | ' '::bpchar
array_val | bytea | | |
Indexes:
"sample_time_cy_idx" btree (channel_id, smpl_time)
"sample_time_yc1_idx" btree (smpl_time, channel_id)
"smpl_time_cmx2_idx" btree (smpl_time)
Check constraints:
"sample_ctrl_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_ctrl
css_archive_3_0_0=# \d sample_buil_year
Table "public.sample_buil_year"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-------------
channel_id | bigint | | not null |
smpl_time | timestamp without time zone | | not null |
nanosecs | bigint | | not null |
severity_id | bigint | | not null |
status_id | bigint | | not null |
num_val | integer | | |
float_val | double precision | | |
str_val | character varying(120) | | |
datatype | character(1) | | | ' '::bpchar
array_val | bytea | | |
Indexes:
"sample_time_by_idx" btree (channel_id, smpl_time)
"sample_time_yb1_idx" btree (smpl_time, channel_id)
"smpl_time_bx2_idx" btree (smpl_time)
Check constraints:
"sample_buil_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_buil
css_archive_3_0_0=#
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 14:34 ` Abadie Lana <Lana.Abadie@iter.org>
2 siblings, 0 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-03 14:34 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: pgsql-performance-owner+M22888-112298@lists.postgresql.org <pgsql-performance-owner+M22888-112298@lists.postgresql.org> On Behalf Of Abadie Lana
Sent: 03 January 2019 14:21
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: [Possible Spoof] RE: select query does not pick up the right index
Warning: This message was sent by pgsql-performance-owner+M22888-112298@lists.postgresql.org supposedly on behalf of Abadie Lana <Lana.Abadie@iter.org>. Please contact
Lana ABADIE
Database Engineer
CODAC Section
ITER Organization, Building 72/4108, SCOD, Control System Division Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 03 January 2019 14:18
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
> From: David Rowley <david.rowley@2ndquadrant.com>
> Sent: 03 January 2019 14:01
> That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
>
> explain (analyze,buffers) select
> 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c
> .num_val,c.str_val,c.datatype,c.array_val from sample c WHERE
> c.channel_id = (SELECT channel_id FROM channel WHERE
> name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc
> limit 5;
> -> Index Scan Backward using smpl_time_bx2_idx on
> sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364
> width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 50597834
> Buffers: shared hit=25913147 read=713221
> -> Index Scan Backward using sample_time_cm_idx on
> sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562
> width=77) (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared read=4
> -> Index Scan Backward using smpl_time_cmx2_idx on
> sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186
> width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 79579075
> Buffers: shared hit=49868991 read=1121715
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
\d sample_ctrl_year
Table "public.sample_ctrl_year"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+---
channel_id | bigint | | not null |
smpl_time | timestamp without time zone | | not null |
nanosecs | bigint | | not null |
severity_id | bigint | | not null |
status_id | bigint | | not null |
num_val | integer | | |
float_val | double precision | | |
str_val | character varying(120) | | |
datatype | character(1) | | | ' '::bpchar
array_val | bytea | | |
Indexes:
"sample_time_cy_idx" btree (channel_id, smpl_time)
"sample_time_yc1_idx" btree (smpl_time, channel_id)
"smpl_time_cmx2_idx" btree (smpl_time) Check constraints:
"sample_ctrl_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_ctrl
css_archive_3_0_0=# \d sample_buil_year
Table "public.sample_buil_year"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+---
channel_id | bigint | | not null |
smpl_time | timestamp without time zone | | not null |
nanosecs | bigint | | not null |
severity_id | bigint | | not null |
status_id | bigint | | not null |
num_val | integer | | |
float_val | double precision | | |
str_val | character varying(120) | | |
datatype | character(1) | | | ' '::bpchar
array_val | bytea | | |
Indexes:
"sample_time_by_idx" btree (channel_id, smpl_time)
"sample_time_yb1_idx" btree (smpl_time, channel_id)
"smpl_time_bx2_idx" btree (smpl_time) Check constraints:
"sample_buil_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_buil
css_archive_3_0_0=#
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
In case I'm also posting the explain analyse of the other query
explain (analyze,buffers) select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time +INTERVAL '0 sec' desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
-------------------------------------
Limit (cost=2746650.57..2746650.59 rows=5 width=158) (actual time=119.927..119.929 rows=3 loops=1)
Buffers: shared hit=3 read=531
-> Sort (cost=2746650.57..2746674.66 rows=9636 width=158) (actual time=119.925..119.926 rows=3 loops=1)
Sort Key: ((c.smpl_time + '00:00:00'::interval)) DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3 read=531
-> Nested Loop (cost=0.00..2746490.52 rows=9636 width=158) (actual time=46.946..119.897 rows=3 loops=1)
Buffers: shared hit=3 read=531
-> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual time=16.217..18.257 rows=1 loops=1)
Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Rows Removed by Filter: 33425
Buffers: shared hit=1 read=497
-> Append (cost=0.00..2684377.38 rows=6117323 width=125) (actual time=30.717..101.624 rows=3 loops=1)
Buffers: shared hit=2 read=34
-> Seq Scan on sample c (cost=0.00..0.00 rows=1 width=334) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (t.channel_id = channel_id)
-> Bitmap Heap Scan on sample_buil c_1 (cost=149.25..10404.32 rows=6300 width=328) (actual time=9.241
..9.242 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Index Scan on sample_time_b_idx (cost=0.00..147.68 rows=6300 width=0) (actual time=9.
237..9.237 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Heap Scan on sample_ctrl c_2 (cost=781.30..11912.06 rows=33661 width=328) (actual time=0.02
0..0.020 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Index Scan on sample_time_c_idx (cost=0.00..772.88 rows=33661 width=0) (actual time=0
.018..0.018 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Heap Scan on sample_util c_3 (cost=221.93..25401.37 rows=9483 width=328) (actual time=7.888
..7.888 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Index Scan on sample_time_u_idx (cost=0.00..219.56 rows=9483 width=0) (actual time=7.
886..7.886 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=3
-> Bitmap Heap Scan on sample_buil_month c_4 (cost=366.32..47118.08 rows=15711 width=82) (actual time
=13.556..24.870 rows=3 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Heap Blocks: exact=3
Buffers: shared read=7
-> Bitmap Index Scan on sample_time_bm_idx (cost=0.00..362.39 rows=15711 width=0) (actual time=
6.712..6.712 rows=3 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Heap Scan on sample_buil_year c_5 (cost=73216.89..687718.44 rows=3162364 width=328) (actual
time=18.015..18.015 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Index Scan on sample_time_by_idx (cost=0.00..72426.29 rows=3162364 width=0) (actual t
ime=18.011..18.011 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Heap Scan on sample_ctrl_month c_6 (cost=12446.67..226848.19 rows=537562 width=85) (actual
time=0.029..0.029 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Index Scan on sample_time_cm_idx (cost=0.00..12312.28 rows=537562 width=0) (actual ti
me=0.026..0.026 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Heap Scan on sample_ctrl_year c_7 (cost=48486.51..978945.83 rows=2094186 width=76) (actual
time=23.088..23.088 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Index Scan on sample_time_cy_idx (cost=0.00..47962.96 rows=2094186 width=0) (actual t
ime=23.086..23.086 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared read=4
-> Bitmap Heap Scan on sample_util_month c_8 (cost=2249.10..277115.63 rows=97101 width=82) (actual ti
me=7.623..7.623 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared hit=1 read=3
-> Bitmap Index Scan on sample_time_um_idx (cost=0.00..2224.82 rows=97101 width=0) (actual time
=7.619..7.619 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared hit=1 read=3
-> Bitmap Heap Scan on sample_util_year c_9 (cost=3727.96..418913.45 rows=160954 width=83) (actual ti
me=10.815..10.815 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Buffers: shared hit=1 read=3
-> Bitmap Index Scan on sample_time_uy_idx (cost=0.00..3687.72 rows=160954 width=0) (actual tim
e=10.811..10.811 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Buffers: shared hit=1 read=3
Planning time: 15.656 ms
Execution time: 120.062 ms
(73 rows)
css_archive_3_0_0=#
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 21:42 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-04 08:10 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2 siblings, 1 reply; 21+ messages in thread
From: David Rowley @ 2019-01-03 21:42 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 21:42 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
@ 2019-01-04 08:10 ` Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 0 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-04 08:10 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----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 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).
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-03 23:47 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2 siblings, 1 reply; 21+ messages in thread
From: Justin Pryzby @ 2019-01-03 23:47 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB, work_mem 4MB
I doubt it will help much, but you should consider increasing work_mem, unless
you have many expensive queries running at once.
Could you also send the rest of the pg_statistic for that table ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
@ 2019-01-04 08:17 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-04 08:17 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 04 January 2019 00:48
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB,
> work_mem 4MB
I doubt it will help much, but you should consider increasing work_mem, unless you have many expensive queries running at once.
Could you also send the rest of the pg_statistic for that table ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm surprised to see sample_{ctrl,util,buil} quoted twice
css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv | n_hist
----------+-------------------+------------+-----------+------------+-------+--------
1 | sample_buil_year | channel_id | 0 | 16 | 16 |
0.98249 | sample_ctrl | channel_id | 0 | 26 | 17 | 9
0.982333 | sample_ctrl_month | channel_id | 0 | 34 | 17 | 17
0.981533 | sample_ctrl | channel_id | 0 | 28 | 18 | 10
0.9371 | sample_ctrl_year | channel_id | 0 | 38 | 16 | 22
0.928767 | sample_buil_month | channel_id | 0 | 940 | 54 | 101
0.92535 | sample | channel_id | 0 | 2144 | 167 | 1001
0.907501 | sample_buil | channel_id | 0 | 565 | 43 | 101
0.8876 | sample_util_year | channel_id | 0 | 501 | 45 | 101
0.815 | sample_util | channel_id | 0 | 557 | 82 | 101
0.807667 | sample_buil | channel_id | 0 | 164 | 31 | 101
0.806267 | sample_util | channel_id | 0 | 732 | 100 | 101
0.803766 | sample_util_month | channel_id | 0 | 731 | 100 | 101
(13 rows)
Ah...sample_ctrl_year and sample_buil_year have n_distinct -1? Unlike sample_util_year. Could that explain the wrong choice?
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='smpl_time' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv | n_hist
------------+-------------------+-----------+-----------+-------------+-------+--------
| sample_ctrl_month | smpl_time | 0 | -1 | | 101
| sample_ctrl_year | smpl_time | 0 | -1 | | 101
| sample_ctrl | smpl_time | 0 | -1 | | 101
| sample_ctrl | smpl_time | 0 | -1 | | 101
| sample_buil_year | smpl_time | 0 | -1 | | 101
0.0154667 | sample_buil_month | smpl_time | 0 | 1.03857e+06 | 100 | 101
0.0154523 | sample_buil | smpl_time | 0 | 854250 | 100 | 101
0.0115 | sample_util | smpl_time | 0 | 405269 | 100 | 101
0.0112333 | sample_util | smpl_time | 0 | 537030 | 100 | 101
0.0106667 | sample_util_month | smpl_time | 0 | 539001 | 100 | 101
0.00946667 | sample_buil | smpl_time | 0 | -0.328554 | 100 | 101
0.00852342 | sample | smpl_time | 0 | 1.5125e+07 | 1000 | 1001
0.00780001 | sample_util_year | smpl_time | 0 | 1.73199e+06 | 100 | 101
(13 rows)
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-04 08:58 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-04 08:58 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----Original Message-----
From: pgsql-performance-owner+M22888-112441@lists.postgresql.org <pgsql-performance-owner+M22888-112441@lists.postgresql.org> On Behalf Of Abadie Lana
Sent: 04 January 2019 09:18
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Subject: [Possible Spoof] RE: select query does not pick up the right index
Warning: This message was sent by pgsql-performance-owner+M22888-112441@lists.postgresql.org supposedly on behalf of Abadie Lana <Lana.Abadie@iter.org>. Please contact
-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 04 January 2019 00:48
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB,
> work_mem 4MB
I doubt it will help much, but you should consider increasing work_mem, unless you have many expensive queries running at once.
Could you also send the rest of the pg_statistic for that table ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm surprised to see sample_{ctrl,util,buil} quoted twice
css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv | n_hist
----------+-------------------+------------+-----------+------------+-------+--------
1 | sample_buil_year | channel_id | 0 | 16 | 16 |
0.98249 | sample_ctrl | channel_id | 0 | 26 | 17 | 9
0.982333 | sample_ctrl_month | channel_id | 0 | 34 | 17 | 17
0.981533 | sample_ctrl | channel_id | 0 | 28 | 18 | 10
0.9371 | sample_ctrl_year | channel_id | 0 | 38 | 16 | 22
0.928767 | sample_buil_month | channel_id | 0 | 940 | 54 | 101
0.92535 | sample | channel_id | 0 | 2144 | 167 | 1001
0.907501 | sample_buil | channel_id | 0 | 565 | 43 | 101
0.8876 | sample_util_year | channel_id | 0 | 501 | 45 | 101
0.815 | sample_util | channel_id | 0 | 557 | 82 | 101
0.807667 | sample_buil | channel_id | 0 | 164 | 31 | 101
0.806267 | sample_util | channel_id | 0 | 732 | 100 | 101
0.803766 | sample_util_month | channel_id | 0 | 731 | 100 | 101
(13 rows)
Ah...sample_ctrl_year and sample_buil_year have n_distinct -1? Unlike sample_util_year. Could that explain the wrong choice?
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='smpl_time' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv | n_hist
------------+-------------------+-----------+-----------+-------------+-------+--------
| sample_ctrl_month | smpl_time | 0 | -1 | | 101
| sample_ctrl_year | smpl_time | 0 | -1 | | 101
| sample_ctrl | smpl_time | 0 | -1 | | 101
| sample_ctrl | smpl_time | 0 | -1 | | 101
| sample_buil_year | smpl_time | 0 | -1 | | 101
0.0154667 | sample_buil_month | smpl_time | 0 | 1.03857e+06 | 100 | 101
0.0154523 | sample_buil | smpl_time | 0 | 854250 | 100 | 101
0.0115 | sample_util | smpl_time | 0 | 405269 | 100 | 101
0.0112333 | sample_util | smpl_time | 0 | 537030 | 100 | 101
0.0106667 | sample_util_month | smpl_time | 0 | 539001 | 100 | 101
0.00946667 | sample_buil | smpl_time | 0 | -0.328554 | 100 | 101
0.00852342 | sample | smpl_time | 0 | 1.5125e+07 | 1000 | 1001
0.00780001 | sample_util_year | smpl_time | 0 | 1.73199e+06 | 100 | 101
(13 rows)
Based on your feedback...i rerun analyse directly on the two table sample_ctrl_year and sample_buil_year
The new values are
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv | n_hist
----------+-------------------+------------+-----------+------------+-------+--------
0.99987 | sample_buil_year | channel_id | 0 | 76 | 16 | 60
0.999632 | sample_ctrl_year | channel_id | 0 | 132 | 31 | 101
0.999628 | sample_ctrl_month | channel_id | 0 | 84 | 23 | 61
0.999627 | sample_ctrl | channel_id | 0 | 132 | 31 | 101
0.999599 | sample_ctrl | channel_id | 0 | 42 | 22 | 20
0.998074 | sample_buil | channel_id | 0 | 493 | 122 | 371
0.997693 | sample_util | channel_id | 0 | 1379 | 509 | 870
0.991841 | sample_buil | channel_id | 0 | 9867 | 107 | 9740
0.991567 | sample_util_month | channel_id | 0 | 5716 | 504 | 5209
0.990369 | sample_util_year | channel_id | 0 | 4946 | 255 | 4689
0.990062 | sample_util | channel_id | 0 | 5804 | 641 | 5160
0.972386 | sample_buil_month | channel_id | 0 | 19946 | 148 | 10001
0.967391 | sample | channel_id | 0 | 7597 | 409 | 7178
(13 rows)
Now when running the query again, only for sample_buil_year table the wrong index is picked up...
explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
Limit (cost=13.40..30.01 rows=5 width=112) (actual time=13554.536..13554.570 rows=3 loops=1)
Buffers: shared hit=26626389 read=17
InitPlan 1 (returns $0)
-> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=26.858..26.860 rows=1 loop
s=1)
Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Buffers: shared hit=2 read=2
-> Result (cost=4.96..5131208.65 rows=1544048 width=112) (actual time=13554.534..13554.567 rows=3 loops=1)
Buffers: shared hit=26626389 read=17
-> Merge Append (cost=4.96..5115768.17 rows=1544048 width=80) (actual time=13554.531..13554.562 rows=3 loops=1)
Sort Key: c.smpl_time DESC
Buffers: shared hit=26626389 read=17
-> Index Scan Backward using smpl_time_qa_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual time=0
.005..0.005 rows=0 loops=1)
Filter: (channel_id = $0)
Buffers: shared hit=1
-> Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..7775.26 rows=2096 width=320)
(actual time=38.931..38.932 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3 read=4
-> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..77785.57 rows=22441 width=320
) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3
-> Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..14922.72 rows=3830 width=320)
(actual time=8.939..8.939 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=1 read=2
-> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width
=74) (actual time=260.282..260.311 rows=3 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3 read=5
-> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023054.76 rows=665761 w
idth=75) (actual time=13216.589..13216.589 rows=0 loops=1)
Filter: (channel_id = $0)
Rows Removed by Filter: 50597834
Buffers: shared hit=26626368
-> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..759241.36 rows=217585
width=75) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
-> Index Scan Backward using sample_time_cy_idx on sample_ctrl_year c_7 (cost=0.57..2097812.02 rows=602872
width=76) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
-> Index Scan Backward using sample_time_um_idx on sample_util_month c_8 (cost=0.57..48401.65 rows=12418 wi
dth=75) (actual time=18.999..19.000 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=1 read=3
-> Index Scan Backward using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..54293.22 rows=16304 wid
th=74) (actual time=10.739..10.739 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=1 read=3
Planning time: 0.741 ms
Execution time: 13554.666 ms
(44 rows)
Looking more closely to the sample_buil_year table
select count(distinct channel_id),count(*) from sample_buil_year;
count | count
-------+----------
100 | 50597834
(1 row)
Now, the channel name I gave has no entries in sample_buil_year...(and when I run the query directly against sample_buil_year the right index is picked up).... So maybe something related with the partitioning?
select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample_buil_year c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
?column? | smpl_time | nanosecs | float_val | num_val | str_val | datatype | array_val
----------+-----------+----------+-----------+---------+---------+----------+-----------
(0 rows)
css_archive_3_0_0=# explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample_buil_year c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
---------------------------------------
Limit (cost=9.00..21.31 rows=5 width=107) (actual time=0.055..0.055 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=0.038..0.040 rows=1 loops=
1)
Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
-> Index Scan Backward using sample_time_by_idx on sample_buil_year c (cost=0.56..1639944.37 rows=665761 width=107) (ac
tual time=0.054..0.054 rows=0 loops=1)
Index Cond: (channel_id = $0)
Planning time: 0.178 ms
Execution time: 0.088 ms
(8 rows)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-05 04:23 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: Justin Pryzby @ 2019-01-05 04:23 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote:
> SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
>
> Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm surprised to see sample_{ctrl,util,buil} quoted twice
One of the rows is for "inherited stats" (including child tables) stats and one
is "noninherited stats".
The unique index on the table behind that view is:
"pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, stainherit)
On the wiki, I added inherited and correlation columns. Would you rerun that query ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
I'm also interested to see \d and channel_id statistics for the channel table.
> explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
You originally wrote this as a implicit comma join. Does the original query
still have an issue ? The =(subselect query) doesn't allow the planner to
optimize for the given channel, which seems to be a fundamental problem.
On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote:
> Based on your feedback...i rerun analyse directly on the two table sample_ctrl_year and sample_buil_year
> [...] Now when running the query again, only for sample_buil_year table the wrong index is picked up...
It looks like statistics on your tables were completely wrong; not just
sample_ctrl_year and sample_buil_year. Right ?
Autoanalyze would normally handle this on nonempty tables (children or
otherwise) and you should manually run ANALZYE on the parents (both levels of
them) whenever statistics change, like after running a big DELETE or DROP or
after a significant interval of time has passed relative to the range of time
in the table's timestamp columns.
Do you know why autoanalze didn't handle the nonempty tables on its own ?
> Now, the channel name I gave has no entries in sample_buil_year...(and when I run the query directly against sample_buil_year the right index is picked up).... So maybe something related with the partitioning?
> -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023054.76 rows=665761 width=75) (actual time=13216.589..13216.589 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 50597834
> Buffers: shared hit=26626368
So it scanned the entire index expecting to find 5 matching channel IDs "pretty
soon", based on the generic distribution of channel IDs, without the benefit of
knowing that this channel ID doesn't exist at all (due to =(subquery)).
26e6 buffers is 200GB, apparently accessing some pages many
times (even if cached).
table_name | index_name | table_size | index_size
sample_buil_year | smpl_time_bx2_idx | 4492 MB | 1084 MB
General comments:
On Wed, Jan 02, 2019 at 04:28:41PM +0000, Abadie Lana wrote:
> "sample_time_bm_idx" btree (channel_id, smpl_time)
> "sample_time_mb1_idx" btree (smpl_time, channel_id)
> "smpl_time_bx1_idx" btree (smpl_time)
The smpl_time index is loosely redundant with index on (smpl_time,channel_id).
You might consider dropping it, or otherwise dropping the smpl_time,channel_id
index and making two separate indices on smpl_time and channel. That would
allow bitmap ANDing them together.
Or possibly (depending on detail of your data loading) leaving the composite
index and changing smpl_time to a BRIN index - it's nice to be able to CLUSTER
on the btree index to maximize the efficiency of the brin index.
>Check constraints:
> "sample_buil_month_smpl_time_check" CHECK (smpl_time >= (now() - '32 days'::interval)::timestamp without time zone AND smpl_time <= now())
I'm surprised that works, and not really sure what it's doing..but in any case
it's maybe not doing what you wanted(??). I'm guessing you never get
constraint exclusion (which is irrelevant for this query but still).
Justin
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
@ 2019-01-07 16:09 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-08 08:15 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-07 16:09 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 05 January 2019 05:24
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote:
> SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
> tablename, attname, null_frac, n_distinct,
> array_length(most_common_vals,1) n_mcv,
> array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE
> attname='...' AND tablename='...' ORDER BY 1 DESC;
>
> Hmm. Is it normal that the couple (tablename,attname ) is not unique?
> I'm surprised to see sample_{ctrl,util,buil} quoted twice
One of the rows is for "inherited stats" (including child tables) stats and one is "noninherited stats".
The unique index on the table behind that view is:
"pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, stainherit)
On the wiki, I added inherited and correlation columns. Would you rerun that query ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
/*********************REPLY**********************************************************/
css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='smpl_time' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation
-------------+-------------------+-----------+-----------+-----------+------------+-------+--------+-------------
0.124457 | sample_buil | smpl_time | f | 0 | -0.752503 | 10000 | 10001 | 0.0802559
0.100454 | sample_util | smpl_time | f | 0 | -0.323349 | 10000 | 10001 | 0.614187
0.0393624 | sample_buil_month | smpl_time | f | 0 | -0.617567 | 10000 | 10001 | 0.181361
0.0305711 | sample_util_month | smpl_time | f | 0 | -0.169437 | 10000 | 10001 | 0.781718
0.0194441 | sample_util_year | smpl_time | f | 0 | -0.428909 | 10000 | 10001 | 0.999893
0.0172493 | sample_util | smpl_time | t | 0 | -0.179957 | 10000 | 10001 | -0.563603
0.0117653 | sample | smpl_time | t | 0 | -0.235397 | 10000 | 10001 | 0.0880253
0.0116284 | sample_buil | smpl_time | t | 0 | -0.743071 | 10000 | 10001 | -0.100979
2.66667e-05 | sample_ctrl_month | smpl_time | f | 0 | -0.999848 | 32 | 10001 | -0.356626
8.48788e-06 | sample_ctrl | smpl_time | f | 0 | -0.999996 | 4 | 10001 | 0.331492
6.33333e-06 | sample_ctrl_year | smpl_time | f | 0 | -0.999835 | 9 | 10001 | 0.999971
5.33333e-06 | sample_ctrl | smpl_time | t | 0 | -0.999827 | 8 | 10001 | 0.0492292
5e-06 | sample_buil_year | smpl_time | f | 0 | -0.999918 | 7 | 10001 | 0.999978
(13 rows)
css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation
----------+-------------------+------------+-----------+-----------+------------+-------+--------+-------------
0.99987 | sample_buil_year | channel_id | f | 0 | 76 | 16 | 60 | 0.207932
0.999632 | sample_ctrl_year | channel_id | f | 0 | 132 | 31 | 101 | 0.201352
0.999628 | sample_ctrl_month | channel_id | f | 0 | 84 | 23 | 61 | 0.104656
0.999627 | sample_ctrl | channel_id | t | 0 | 132 | 31 | 101 | 0.143691
0.999599 | sample_ctrl | channel_id | f | 0 | 42 | 22 | 20 | 0.0874279
0.998074 | sample_buil | channel_id | f | 0 | 493 | 122 | 371 | 0.0206452
0.997693 | sample_util | channel_id | f | 0 | 1379 | 509 | 870 | 0.079591
0.991841 | sample_buil | channel_id | t | 0 | 9867 | 107 | 9740 | 0.00540782
0.991567 | sample_util_month | channel_id | f | 0 | 5716 | 504 | 5209 | 0.216868
0.990369 | sample_util_year | channel_id | f | 0 | 4946 | 255 | 4689 | 0.547934
0.990062 | sample_util | channel_id | t | 0 | 5804 | 641 | 5160 | -0.31778
0.972386 | sample_buil_month | channel_id | f | 0 | 19946 | 148 | 10001 | 0.0932767
0.967391 | sample | channel_id | t | 0 | 7597 | 409 | 7178 | 0.501865
(13 rows)
css_archive_3_0_0=
/**********************ENDREPLY************************************************/
I'm also interested to see \d and channel_id statistics for the channel table.
/***********************REPLY***********************************************/
\d channel
Table "public.channel"
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+-----------------------------------
channel_id | bigint | | not null | nextval('channel_chid'::regclass)
name | character varying(100) | | not null |
descr | character varying(100) | | |
grp_id | bigint | | |
smpl_mode_id | bigint | | |
smpl_val | double precision | | |
smpl_per | double precision | | |
retent_id | bigint | | | 1
retent_val | double precision | | |
Indexes:
"channel_pkey" PRIMARY KEY, btree (channel_id)
"unique_chname" UNIQUE CONSTRAINT, btree (name)
"channel_name_channel_id_idx" btree (name, channel_id)
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname in ('name','channel_id') AND tablename ='channel' ORDER BY 1 DESC;
frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation
----------+-----------+------------+-----------+-----------+------------+-------+--------+-------------
| channel | channel_id | f | 0 | -1 | | 10001 | 0.0200338
| channel | name | f | 0 | -1 | | 10001 | -0.257645
/*********************ENDREPLY****************************************************************/
> explain (analyze, buffers) select
> 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c
> .num_val,c.str_val,c.datatype,c.array_val from sample c WHERE
> c.channel_id = (SELECT channel_id FROM channel WHERE
> name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc
> limit 5;
You originally wrote this as a implicit comma join. Does the original query still have an issue ? The =(subselect query) doesn't allow the planner to optimize for the given channel, which seems to be a fundamental problem.
/****************************REPLY***************************************************/
Yes the original query still picks up the wrong index. This query actually was suggested by David Rowley and actually with this one the planner is taking the wring index for only sample_ctrl_year and sample_buil_year tables. With some proper analyse, now only sample_ctrl_year.
/*****************************ENDREPLY**************************************************/
On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote:
> Based on your feedback...i rerun analyse directly on the two table
> sample_ctrl_year and sample_buil_year [...] Now when running the query again, only for sample_buil_year table the wrong index is picked up...
It looks like statistics on your tables were completely wrong; not just sample_ctrl_year and sample_buil_year. Right ?
/*****************************REPLY*******************************************************/
I would say that when you have a partitioned table, running analyse on the parent table (which includes the children) does not give the same result as running analyse on each individual child table. I don't know if it is an expected behaviour?
/********************************ENDREPLY****************************************************/
Autoanalyze would normally handle this on nonempty tables (children or
otherwise) and you should manually run ANALZYE on the parents (both levels of
them) whenever statistics change, like after running a big DELETE or DROP or after a significant interval of time has passed relative to the range of time in the table's timestamp columns.
Do you know why autoanalze didn't handle the nonempty tables on its own ?
/******************************REPLY***************************************************************/
This database has been loaded via a dump. After there was no change in the actual tables'content apart from creating/droping.
indexes.
So I guess that's why autoanalyze didn't run (also I didn't change the default configuration for this part in postgresql.conf)
/*******************************ENDREPLY**********************************************************/
> Now, the channel name I gave has no entries in sample_buil_year...(and when I run the query directly against sample_buil_year the right index is picked up).... So maybe something related with the partitioning?
> -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023054.76 rows=665761 width=75) (actual time=13216.589..13216.589 rows=0 loops=1)
> Filter: (channel_id = $0)
> Rows Removed by Filter: 50597834
> Buffers: shared hit=26626368
So it scanned the entire index expecting to find 5 matching channel IDs "pretty soon", based on the generic distribution of channel IDs, without the benefit of knowing that this channel ID doesn't exist at all (due to =(subquery)).
/*********************************REPLY******************************************************/
Exactly it took hearethe wrong index smpl_time_bx2_idx instead of sample_time_by_idx.
/*********************************ENDREPLY**************************************************/
26e6 buffers is 200GB, apparently accessing some pages many times (even if cached).
/**********************************REPLY********************************************************/
Yes this is what I observed when running iotop...more than 17GB was read from disk. I'm surprised as I would expect that the max. would be the index size...~7GB. We also get an swap alert...because it uses swap...
/********************************ENDREPLY**************************************************/
table_name | index_name | table_size | index_size
sample_buil_year | smpl_time_bx2_idx | 4492 MB | 1084 MB
General comments:
On Wed, Jan 02, 2019 at 04:28:41PM +0000, Abadie Lana wrote:
> "sample_time_bm_idx" btree (channel_id, smpl_time)
> "sample_time_mb1_idx" btree (smpl_time, channel_id)
> "smpl_time_bx1_idx" btree (smpl_time)
The smpl_time index is loosely redundant with index on (smpl_time,channel_id).
You might consider dropping it, or otherwise dropping the smpl_time,channel_id index and making two separate indices on smpl_time and channel. That would allow bitmap ANDing them together.
/******************************REPLY***********************************************************/
Yes I know. The thing is I had to find a quick solution to fix as my application was taking ages - two types of queries (one which requires channeld_id=XX + order by time and another one by time range (all channels between T1 and T2).
As the smpl_time_bx1_idx was slowing down the first query, I created sample_time_mb1_idx and drop smpl_time_bx1_idx.
Now it has been recreated as I wanted to understand why the planner picked up the wrong indexes.
/*****************************ENDREPLY**********************************************************/
Or possibly (depending on detail of your data loading) leaving the composite index and changing smpl_time to a BRIN index - it's nice to be able to CLUSTER on the btree index to maximize the efficiency of the brin index.
>Check constraints:
> "sample_buil_month_smpl_time_check" CHECK (smpl_time >= (now() -
>'32 days'::interval)::timestamp without time zone AND smpl_time <=
>now())
I'm surprised that works, and not really sure what it's doing..but in any case it's maybe not doing what you wanted(??). I'm guessing you never get constraint exclusion (which is irrelevant for this query but still).
/*********************************REPLY************************************************/
I know that the partitioning is not exclusive in this one. In fact the insert is done at sample_{util/buil/ctrl} table. The data is in this table. Then there are some scripts which moves data from sample -> sample_month and then sample_month-> sample_year.
I'm not the owner of this schema...so cannot comment why it has been done like that...
And same for indexes. I cannot change them.
I did it in that case, because I did a copy of the database and launched the apps on this one (part of annual maintenance activities).
I created the BRIN index on smpl_time and now the original query runs fine because it uses the right index, the one on (channel_id,smpl_time)
explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
Limit (cost=1869725.53..1869725.54 rows=5 width=113) (actual time=3.898..3.900 rows=3 loops=1)
-> Sort (cost=1869725.53..1869749.62 rows=9636 width=113) (actual time=3.896..3.897 rows=3 loops=1)
Sort Key: c.smpl_time DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..1869565.48 rows=9636 width=113) (actual time=2.270..3.878 rows=3 loops=1)
-> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual time=2.212..3.773 rows=1 loops=1)
Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Rows Removed by Filter: 33425
-> Append (cost=0.00..1853209.17 rows=1544048 width=88) (actual time=0.053..0.099 rows=3 loops=1)
-> Seq Scan on sample c (cost=0.00..0.00 rows=1 width=334) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (t.channel_id = channel_id)
-> Bitmap Heap Scan on sample_buil c_1 (cost=52.67..5440.29 rows=2096 width=328) (actual time=0.016.
.0.016 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_b_idx (cost=0.00..52.14 rows=2096 width=0) (actual time=0.
008..0.008 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_ctrl c_2 (cost=522.34..11512.86 rows=22441 width=328) (actual time=0.0
05..0.006 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_c_idx (cost=0.00..516.73 rows=22441 width=0) (actual time=
0.005..0.005 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_util c_3 (cost=90.11..12215.14 rows=3830 width=328) (actual time=0.009
..0.009 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_u_idx (cost=0.00..89.16 rows=3830 width=0) (actual time=0.
006..0.006 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_buil_month c_4 (cost=18.29..2836.29 rows=740 width=82) (actual time=0.
017..0.021 rows=3 loops=1)
Recheck Cond: (channel_id = t.channel_id)
Heap Blocks: exact=3
-> Bitmap Index Scan on sample_time_bm_idx (cost=0.00..18.11 rows=740 width=0) (actual time=0.
012..0.012 rows=3 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_buil_year c_5 (cost=15416.21..627094.50 rows=665761 width=83) (actual
time=0.008..0.008 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_by_idx (cost=0.00..15249.77 rows=665761 width=0) (actual t
ime=0.007..0.007 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_ctrl_month c_6 (cost=5038.85..223721.75 rows=217585 width=83) (actual
time=0.006..0.007 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_cm_idx (cost=0.00..4984.45 rows=217585 width=0) (actual ti
me=0.006..0.006 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_ctrl_year c_7 (cost=13960.83..870933.00 rows=602872 width=84) (actual
time=0.006..0.006 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_cy_idx (cost=0.00..13810.11 rows=602872 width=0) (actual t
ime=0.005..0.015 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Bitmap Heap Scan on sample_util_month c_8 (cost=288.81..45162.12 rows=12418 width=83) (actual tim
e=0.008..0.008 rows=0 loops=1)
Recheck Cond: (channel_id = t.channel_id)
-> Bitmap Index Scan on sample_time_um_idx (cost=0.00..285.70 rows=12418 width=0) (actual time
=0.007..0.007 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
-> Index Scan using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..54293.22 rows=16304 width
=82) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (channel_id = t.channel_id)
Planning time: 1.752 ms
Execution time: 4.004
But not the other query...still time-consuming because still using the wrong index in case of sample_buil_year (but curiously not the BRIN index)
explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------
Limit (cost=13.40..30.54 rows=5 width=112) (actual time=63411.725..63411.744 rows=3 loops=1)
Buffers: shared hit=38 read=193865
InitPlan 1 (returns $0)
-> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops
=1)
Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Buffers: shared hit=4
-> Result (cost=4.96..5294364.58 rows=1544048 width=112) (actual time=63411.723..63411.740 rows=3 loops=1)
Buffers: shared hit=38 read=193865
-> Merge Append (cost=4.96..5278924.10 rows=1544048 width=80) (actual time=63411.719..63411.735 rows=3 loops=1)
Sort Key: c.smpl_time DESC
Buffers: shared hit=38 read=193865
-> Index Scan Backward using sample_time_all_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual ti
me=0.048..0.048 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=5
-> Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..7775.26 rows=2096 width=320)
(actual time=0.008..0.009 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3
-> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..77785.57 rows=22441 width=32
0) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3
-> Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..14922.72 rows=3830 width=320
) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=3
-> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 widt
h=74) (actual time=0.011..0.025 rows=3 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=8
-> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5 (cost=0.56..2186210.68 rows=66576
1 width=75) (actual time=63411.573..63411.574 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=193865
-> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..759241.36 rows=217585
width=75) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
-> Index Scan Backward using sample_time_cy_idx on sample_ctrl_year c_7 (cost=0.57..2097812.02 rows=602872
width=76) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
-> Index Scan Backward using sample_time_um_idx on sample_util_month c_8 (cost=0.57..48401.65 rows=12418 w
idth=75) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
-> Index Scan Backward using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..54293.22 rows=16304 wi
dth=74) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared hit=4
Planning time: 0.727 ms
Execution time: 63411.858 ms
(43 rows)
\d sample_buil_year
Table "public.sample_buil_year"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-------------
channel_id | bigint | | not null |
smpl_time | timestamp without time zone | | not null |
nanosecs | bigint | | not null |
severity_id | bigint | | not null |
status_id | bigint | | not null |
num_val | integer | | |
float_val | double precision | | |
str_val | character varying(120) | | |
datatype | character(1) | | | ' '::bpchar
array_val | bytea | | |
Indexes:
"sample_time_by_idx" btree (channel_id, smpl_time)
"sample_time_yb1_idx" btree (smpl_time, channel_id)
"smpl__by_brin_idx" brin (smpl_time) WITH (pages_per_range='128')
Check constraints:
"sample_buil_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_buil
It works when I dropped the other index sample_time_yb1_idx
The BRIN works well with the other query. Thanks for the tip I will look into more details on this BRIN.
Thanks for your help
/********************************ENDREPLY*********************************************/
Justin
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-08 08:15 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-09 12:55 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: Justin Pryzby @ 2019-01-08 08:15 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, Jan 07, 2019 at 04:09:50PM +0000, Abadie Lana wrote:
> "channel_pkey" PRIMARY KEY, btree (channel_id)
> "unique_chname" UNIQUE CONSTRAINT, btree (name)
> "channel_name_channel_id_idx" btree (name, channel_id)
Note, the third index is more or less redundant.
> I would say that when you have a partitioned table, running analyse on the parent table (which includes the children) does not give the same result as running analyse on each individual child table. I don't know if it is an expected behaviour?
Right, for relkind='r' inheritence, ANALYZE parent gathers 1) stats for the
parent ONLY (stored with pg_stats inherited='f'); and, 2) stats for the parent
and its children (stored in pg_stats with inherited='t').
It *doesn't* update statistics for each of the children themselves. Note
however that for partitions of relkind='p' tables (available since postgres 10)
ANALYZE parent *ALSO* updates stats for the children.
> But not the other query...still time-consuming because still using the wrong index in case of sample_buil_year (but curiously not the BRIN index)
> explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
> Limit (cost=13.40..30.54 rows=5 width=112) (actual time=63411.725..63411.744 rows=3 loops=1)
> Buffers: shared hit=38 read=193865
> InitPlan 1 (returns $0)
> -> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops =1)
> Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
> Buffers: shared hit=4
> -> Result (cost=4.96..5294364.58 rows=1544048 width=112) (actual time=63411.723..63411.740 rows=3 loops=1)
> Buffers: shared hit=38 read=193865
> -> Merge Append (cost=4.96..5278924.10 rows=1544048 width=80) (actual time=63411.719..63411.735 rows=3 loops=1)
> Sort Key: c.smpl_time DESC
> Buffers: shared hit=38 read=193865
> -> Index Scan Backward using sample_time_all_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=5
> -> Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..7775.26 rows=2096 width=320) (actual time=0.008..0.009 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..77785.57 rows=22441 width=320) (actual time=0.006..0.006 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..14922.72 rows=3830 width=320) (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width=74) (actual time=0.011..0.025 rows=3 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=8
> -> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5 (cost=0.56..2186210.68 rows=665761 width=75) (actual time=63411.573..63411.574 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared read=193865
I think I see the issue..
Note, this is different than before.
Initially the query was slow due to reading the indices for the entire
heirarchy, then sorting them, then joining:
| -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5(cost=0.56..1897430.89 rows=50597832 width=328) (actual time=0.068..139840.439 rows=50597834 loops=1)
| -> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c_6 (cost=0.44..55253292.21 rows=18277124 width=85) (actual time=0.061..14610.389 rows=18277123 loops=1)
| -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..2987358.31 rows=79579072 width=76) (actual time=0.067..286316.865 rows=79579075 loops=1)
| -> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_8 (cost=0.57..98830163.45 rows=70980976 width=82) (actual time=0.071..60766.643 rows=70980980 loops=1)
| -> Index Scan Backward using smpl_time_ux2_idx on sample_util_year c_9 (cost=0.57..3070642.94 rows=80637888 width=83) (actual time=0.069..307091.673 rows=80637891 loops=1)
Then you ANALYZEd parent tables and added indices and constraints and started
getting bitmap scans, with new query using David's INTERVAL '0 sec':
| ...
| -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364 width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
| Filter: (channel_id = $0)
| Rows Removed by Filter: 50597834
| Buffers: shared hit=25913147 read=713221
| -> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562 width=77) (actual time=0.048..0.048 rows=0 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared read=4
| -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186 width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
| Filter: (channel_id = $0)
| Rows Removed by Filter: 79579075
| Buffers: shared hit=49868991 read=1121715
| ...
I didn't notice this at first, but compare the two slow scans with the fast one.
The slow scans have no index condition: they're reading the entire index and
FILTERING on channel_id rather than searching the index for it.
Now for the "bad" query you're getting:
| ...
| -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width=74) (actual time=0.011..0.025 rows=3 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared hit=8
| -> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5 (cost=0.56..2186210.68 rows=665761 width=75) (actual time=63411.573..63411.574 rows=0 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared read=193865
| ...
This time, the bad scan *is* kind-of searching on channel_id, but reading the
entire 1500MB index to do it ... because channel_id is not a leading column:
| "sample_time_yb1_idx" btree (smpl_time, channel_id)
And I think the explanation is here:
> css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
> frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation
> ----------+-------------------+------------+-----------+-----------+------------+-------+--------+-------------
> 5e-06 | sample_buil_year | smpl_time | f | 0 | -0.999918 | 7 | 10001 | 0.999978
...
> 0.99987 | sample_buil_year | channel_id | f | 0 | 76 | 16 | 60 | 0.207932
The table is highly correlated in its physical storage WRT correlation, and
poorly correlated WRT channel_id. Thats matter since it thinks the index will
be almost entirely cached, but not the table:
| sample_buil_year | sample_time_yb1_idx | 4492 MB | 1522 MB
So the planner thinks that reading up to 1500MB index from cache will pay off
in ability to read the table sequentially. If it searches the index on
channel_id, it would have to read 665761 tuples across a wide fraction of the
table (a pages here and a page there), defeating readahead, rather than reading
pages clustered/clumped together.
On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB, work_mem 4MB
The issue here may just be that you have effective_cache_size=4GB, so planner
thinks that sample_time_yb1_idx is likely to be cached. Try decreasing that
alot, since it's clearly not cached ? Also,
effective_cache_size==shared_buffers is only accurate if you've allocated the
server's entire RAM to shared_buffers, which is unreasonable. (Or perhaps if
the OS cache is 10x busier with other processes than postgres).
I'm not sure why your query plan changed with a brin indx...it wasn't actually
used, preferring to scan the original index on channel_id, as you hoped.
| -> Bitmap Heap Scan on sample_buil_year c_5 (cost=15416.21..627094.50 rows=665761 width=83) (actual time=0.008..0.008 rows=0 loops=1)
| Recheck Cond: (channel_id = t.channel_id)
| -> Bitmap Index Scan on sample_time_by_idx (cost=0.00..15249.77 rows=665761 width=0) (actual time=0.007..0.007 rows=0 loops=1)
| Index Cond: (channel_id = t.channel_id)
Justin
^ permalink raw reply [nested|flat] 21+ messages in thread
* RE: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-08 08:15 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
@ 2019-01-09 12:55 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-09 16:41 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 21+ messages in thread
From: Abadie Lana @ 2019-01-09 12:55 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 08 January 2019 09:15
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Mon, Jan 07, 2019 at 04:09:50PM +0000, Abadie Lana wrote:
> "channel_pkey" PRIMARY KEY, btree (channel_id)
> "unique_chname" UNIQUE CONSTRAINT, btree (name)
> "channel_name_channel_id_idx" btree (name, channel_id)
Note, the third index is more or less redundant.
> I would say that when you have a partitioned table, running analyse on the parent table (which includes the children) does not give the same result as running analyse on each individual child table. I don't know if it is an expected behaviour?
Right, for relkind='r' inheritence, ANALYZE parent gathers 1) stats for the parent ONLY (stored with pg_stats inherited='f'); and, 2) stats for the parent and its children (stored in pg_stats with inherited='t').
It *doesn't* update statistics for each of the children themselves. Note however that for partitions of relkind='p' tables (available since postgres 10) ANALYZE parent *ALSO* updates stats for the children.
> But not the other query...still time-consuming because still using the
> wrong index in case of sample_buil_year (but curiously not the BRIN
> index) explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; Limit (cost=13.40..30.54 rows=5 width=112) (actual time=63411.725..63411.744 rows=3 loops=1)
> Buffers: shared hit=38 read=193865
> InitPlan 1 (returns $0)
> -> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops =1)
> Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
> Buffers: shared hit=4
> -> Result (cost=4.96..5294364.58 rows=1544048 width=112) (actual time=63411.723..63411.740 rows=3 loops=1)
> Buffers: shared hit=38 read=193865
> -> Merge Append (cost=4.96..5278924.10 rows=1544048 width=80) (actual time=63411.719..63411.735 rows=3 loops=1)
> Sort Key: c.smpl_time DESC
> Buffers: shared hit=38 read=193865
> -> Index Scan Backward using sample_time_all_idx on sample c (cost=0.12..8.14 rows=1 width=326) (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=5
> -> Index Scan Backward using sample_time_b_idx on sample_buil c_1 (cost=0.42..7775.26 rows=2096 width=320) (actual time=0.008..0.009 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_c_idx on sample_ctrl c_2 (cost=0.42..77785.57 rows=22441 width=320) (actual time=0.006..0.006 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_u_idx on sample_util c_3 (cost=0.43..14922.72 rows=3830 width=320) (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=3
> -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width=74) (actual time=0.011..0.025 rows=3 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared hit=8
> -> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5 (cost=0.56..2186210.68 rows=665761 width=75) (actual time=63411.573..63411.574 rows=0 loops=1)
> Index Cond: (channel_id = $0)
> Buffers: shared read=193865
I think I see the issue..
Note, this is different than before.
Initially the query was slow due to reading the indices for the entire heirarchy, then sorting them, then joining:
| -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5(cost=0.56..1897430.89 rows=50597832 width=328) (actual time=0.068..139840.439 rows=50597834 loops=1)
| -> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c_6 (cost=0.44..55253292.21 rows=18277124 width=85) (actual time=0.061..14610.389 rows=18277123 loops=1)
| -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..2987358.31 rows=79579072 width=76) (actual time=0.067..286316.865 rows=79579075 loops=1)
| -> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_8 (cost=0.57..98830163.45 rows=70980976 width=82) (actual time=0.071..60766.643 rows=70980980 loops=1)
| -> Index Scan Backward using smpl_time_ux2_idx on sample_util_year
|c_9 (cost=0.57..3070642.94 rows=80637888 width=83) (actual
|time=0.069..307091.673 rows=80637891 loops=1)
Then you ANALYZEd parent tables and added indices and constraints and started getting bitmap scans, with new query using David's INTERVAL '0 sec':
| ...
| -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 (cost=0.56..2023925.30 rows=3162364 width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
| Filter: (channel_id = $0)
| Rows Removed by Filter: 50597834
| Buffers: shared hit=25913147 read=713221
| -> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6 (cost=0.56..1862587.12 rows=537562 width=77) (actual time=0.048..0.048 rows=0 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared read=4
| -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..3186305.67 rows=2094186 width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
| Filter: (channel_id = $0)
| Rows Removed by Filter: 79579075
| Buffers: shared hit=49868991 read=1121715
| ...
I didn't notice this at first, but compare the two slow scans with the fast one.
The slow scans have no index condition: they're reading the entire index and FILTERING on channel_id rather than searching the index for it.
Now for the "bad" query you're getting:
| ...
| -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width=74) (actual time=0.011..0.025 rows=3 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared hit=8
| -> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5 (cost=0.56..2186210.68 rows=665761 width=75) (actual time=63411.573..63411.574 rows=0 loops=1)
| Index Cond: (channel_id = $0)
| Buffers: shared read=193865
| ...
This time, the bad scan *is* kind-of searching on channel_id, but reading the entire 1500MB index to do it ... because channel_id is not a leading column:
| "sample_time_yb1_idx" btree (smpl_time, channel_id)
And I think the explanation is here:
> css_archive_3_0_0=# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
> frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation
> ----------+-------------------+------------+-----------+-----------+------------+-------+--------+-------------
> 5e-06 | sample_buil_year | smpl_time | f | 0 | -0.999918 | 7 | 10001 | 0.999978
...
> 0.99987 | sample_buil_year | channel_id | f | 0 | 76 | 16 | 60 | 0.207932
The table is highly correlated in its physical storage WRT correlation, and poorly correlated WRT channel_id. Thats matter since it thinks the index will be almost entirely cached, but not the table:
| sample_buil_year | sample_time_yb1_idx | 4492 MB | 1522 MB
So the planner thinks that reading up to 1500MB index from cache will pay off in ability to read the table sequentially. If it searches the index on channel_id, it would have to read 665761 tuples across a wide fraction of the table (a pages here and a page there), defeating readahead, rather than reading pages clustered/clumped together.
On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB,
> work_mem 4MB
The issue here may just be that you have effective_cache_size=4GB, so planner thinks that sample_time_yb1_idx is likely to be cached. Try decreasing that alot, since it's clearly not cached ? Also, effective_cache_size==shared_buffers is only accurate if you've allocated the server's entire RAM to shared_buffers, which is unreasonable. (Or perhaps if the OS cache is 10x busier with other processes than postgres).
I'm not sure why your query plan changed with a brin indx...it wasn't actually used, preferring to scan the original index on channel_id, as you hoped.
| -> Bitmap Heap Scan on sample_buil_year c_5 (cost=15416.21..627094.50 rows=665761 width=83) (actual time=0.008..0.008 rows=0 loops=1)
| Recheck Cond: (channel_id = t.channel_id)
| -> Bitmap Index Scan on sample_time_by_idx (cost=0.00..15249.77 rows=665761 width=0) (actual time=0.007..0.007 rows=0 loops=1)
| Index Cond: (channel_id = t.channel_id)
Justin
Hi,
First I'm using postgresql 10.5, so it means that running analyse on sample table was also triggering a analyse on sample children. However as I said, it is not what I observed. Analyze sample is not the same as analyse children tables. Maybe because in that case it is two-level partitioning, i.e. children of children
I run the tests once more with all your inputs...but this time I change the postgres settings - but no real success
Effective_cache_size=512MB (was 6GB)
Shared_buffers=2GB (was 6GB)
Work=512MB (was 4MB)
original query still expensive (no filter + wrong index) : still I can see some swap activities even though I have plenty of memory....
explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
Limit (cost=5.13..140077.00 rows=5 width=114) (actual time=159467.334..1486064.079 rows=3 loops=1)
-> Nested Loop (cost=5.13..269946514.15 rows=9636 width=114) (actual time=159467.332..1486064.073 rows=3 loops=1)
Join Filter: (c.channel_id = t.channel_id)
Rows Removed by Join Filter: 322099471
-> Merge Append (cost=4.71..265115013.75 rows=322099464 width=89) (actual time=170.874..1205525.136 rows=322099474 loops=1)
Sort Key: c.smpl_time DESC
-> Index Scan Backward using smpl_time_a_idx on sample c (cost=0.12..8.14 rows=1 width=334) (actual time=0.004..0.004 ro
ws=0 loops=1)
-> Index Scan Backward using smpl_time_b_idx on sample_buil c_1 (cost=0.42..4059177.39 rows=1033169 width=328) (actual t
ime=14.487..13290.596 rows=1033169 loops=1)
-> Index Scan Backward using smpl_time_c_idx on sample_ctrl c_2 (cost=0.42..3321314.50 rows=942520 width=328) (actual ti
me=12.598..11553.956 rows=942520 loops=1)
-> Index Scan Backward using smpl_time_u_idx on sample_util c_3 (cost=0.43..13064997.74 rows=5282177 width=328) (actual
time=17.136..33692.383 rows=5282177 loops=1)
-> Index Scan Backward using smpl_time_bm_idx on sample_buil_month c_4 (cost=0.43..56507719.34 rows=14768705 width=82) (
actual time=12.616..69994.281 rows=14768705 loops=1)
-> Index Scan Backward using smpl_time_by_idx on sample_buil_year c_5 (cost=0.56..1897685.68 rows=50597832 width=84) (ac
tual time=33.374..221346.806 rows=50597834 loops=1)
-> Index Scan Backward using smpl_time_cm_idx on sample_ctrl_month c_6 (cost=0.44..63167512.05 rows=18277124 width=84) (
actual time=17.823..80242.045 rows=18277123 loops=1)
-> Index Scan Backward using smpl_time_cy_idx on sample_ctrl_year c_7 (cost=0.57..2988555.40 rows=79579072 width=84) (ac
tual time=18.082..195370.352 rows=79579075 loops=1)
-> Index Scan Backward using smpl_time_um_idx on sample_util_month c_8 (cost=0.57..110877026.27 rows=70980976 width=83)
(actual time=26.942..184412.358 rows=70980980 loops=1)
-> Index Scan Backward using smpl_time_uy_idx on sample_util_year c_9 (cost=0.57..3075812.13 rows=80637888 width=83) (ac
tual time=17.794..275571.960 rows=80637891 loops=1)
-> Materialize (cost=0.41..8.44 rows=1 width=41) (actual time=0.000..0.000 rows=1 loops=322099474)
-> Index Only Scan using channel_name_channel_id_idx on channel t (cost=0.41..8.43 rows=1 width=41) (actual time=15.385.
.15.388 rows=1 loops=1)
Index Cond: (name = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Heap Fetches: 1
Planning time: 1.677 ms
Execution time: 1486064.165 ms
(22 rows)
The other query suggested by D.Rowley has the same issue : still swap activity is higher.
explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c where c.channel_id in (select channel_id from channel where name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
Limit (cost=5.13..140077.00 rows=5 width=113) (actual time=38582.017..1549136.681 rows=3 loops=1)
-> Nested Loop (cost=5.13..269946514.15 rows=9636 width=113) (actual time=38582.014..1549136.674 rows=3 loops=1)
Join Filter: (c.channel_id = channel.channel_id)
Rows Removed by Join Filter: 322099471
-> Merge Append (cost=4.71..265115013.75 rows=322099464 width=89) (actual time=0.437..1269913.701 rows=322099474 loops=1)
Sort Key: c.smpl_time DESC
-> Index Scan Backward using smpl_time_a_idx on sample c (cost=0.12..8.14 rows=1 width=334) (actual time=0.006..0.006 rows=0 loops=1)
-> Index Scan Backward using smpl_time_b_idx on sample_buil c_1 (cost=0.42..4059177.39 rows=1033169 width=328) (actual time=0.055..702.253 ro
ws=1033169 loops=1)
-> Index Scan Backward using smpl_time_c_idx on sample_ctrl c_2 (cost=0.42..3321314.50 rows=942520 width=328) (actual time=0.039..684.282 row
s=942520 loops=1)
-> Index Scan Backward using smpl_time_u_idx on sample_util c_3 (cost=0.43..13064997.74 rows=5282177 width=328) (actual time=0.045..3624.667
rows=5282177 loops=1)
-> Index Scan Backward using smpl_time_bm_idx on sample_buil_month c_4 (cost=0.43..56507719.34 rows=14768705 width=82) (actual time=0.039..65
099.797 rows=14768705 loops=1)
-> Index Scan Backward using smpl_time_by_idx on sample_buil_year c_5 (cost=0.56..1897685.68 rows=50597832 width=84) (actual time=0.053..1173
26.709 rows=50597834 loops=1)
-> Index Scan Backward using smpl_time_cm_idx on sample_ctrl_month c_6 (cost=0.44..63167512.05 rows=18277124 width=84) (actual time=0.037..76
905.550 rows=18277123 loops=1)
-> Index Scan Backward using smpl_time_cy_idx on sample_ctrl_year c_7 (cost=0.57..2988555.40 rows=79579072 width=84) (actual time=0.052..4150
67.696 rows=79579075 loops=1)
-> Index Scan Backward using smpl_time_um_idx on sample_util_month c_8 (cost=0.57..110877026.27 rows=70980976 width=83) (actual time=0.053..1
41602.620 rows=70980980 loops=1)
-> Index Scan Backward using smpl_time_uy_idx on sample_util_year c_9 (cost=0.57..3075812.13 rows=80637888 width=83) (actual time=0.050..3298
99.409 rows=80637891 loops=1)
-> Materialize (cost=0.41..8.44 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=322099474)
-> Index Only Scan using channel_name_channel_id_idx on channel (cost=0.41..8.43 rows=1 width=8) (actual time=0.102..0.103 rows=1 loops=1)
Index Cond: (name = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Heap Fetches: 1
Planning time: 11.566 ms
Execution time: 1549156.273 ms
The query which works the best so far - no swap
explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c where c.channel_id in (select channel_id from channel where name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time + interval '0 sec' desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1865809.94..1865809.95 rows=5 width=121) (actual time=220.854..220.856 rows=3 loops=1)
-> Sort (cost=1865809.94..1865834.03 rows=9636 width=121) (actual time=220.852..220.853 rows=3 loops=1)
Sort Key: ((c.smpl_time + '00:00:00'::interval)) DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..1865649.88 rows=9636 width=121) (actual time=133.087..220.823 rows=3 loops=1)
-> Seq Scan on channel (cost=0.00..915.83 rows=1 width=8) (actual time=19.561..21.602 rows=1 loops=1)
Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Rows Removed by Filter: 33425
-> Append (cost=0.00..1849451.58 rows=1525839 width=89) (actual time=113.510..199.202 rows=3 loops=1)
-> Seq Scan on sample c (cost=0.00..0.00 rows=1 width=334) (actual time=0.010..0.010 rows=0 loops=1)
Filter: (channel.channel_id = channel_id)
-> Bitmap Heap Scan on sample_buil c_1 (cost=52.67..5440.29 rows=2096 width=328) (actual time=12.217..12.217 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_time_b_idx (cost=0.00..52.14 rows=2096 width=0) (actual time=12.207..12.208 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_ctrl c_2 (cost=522.34..11512.86 rows=22441 width=328) (actual time=23.037..23.037 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_time_c_idx (cost=0.00..516.73 rows=22441 width=0) (actual time=23.032..23.033 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_util c_3 (cost=89.99..12171.59 rows=3814 width=328) (actual time=52.641..52.642 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_time_u_idx (cost=0.00..89.04 rows=3814 width=0) (actual time=52.636..52.636 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_buil_month c_4 (cost=18.28..2828.85 rows=738 width=82) (actual time=25.584..25.617 rows=3 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
Heap Blocks: exact=3
-> Bitmap Index Scan on sample_time_bm_idx (cost=0.00..18.09 rows=738 width=0) (actual time=22.164..22.164 rows=3 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_buil_year c_5 (cost=15217.21..626249.52 rows=657115 width=84) (actual time=15.325..15.325 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_by_time_idx (cost=0.00..15052.93 rows=657115 width=0) (actual time=15.319..15.319 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_ctrl_month c_6 (cost=4923.63..222921.32 rows=212525 width=84) (actual time=16.785..16.786 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_time_cm_idx (cost=0.00..4870.50 rows=212525 width=0) (actual time=16.779..16.779 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_ctrl_year c_7 (cost=13853.69..868668.59 rows=598339 width=84) (actual time=21.316..21.316 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_cy_time_idx (cost=0.00..13704.11 rows=598339 width=0) (actual time=21.312..21.312 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Bitmap Heap Scan on sample_util_month c_8 (cost=288.92..45214.06 rows=12433 width=83) (actual time=17.240..17.240 rows=0 loops=1)
Recheck Cond: (channel_id = channel.channel_id)
-> Bitmap Index Scan on sample_time_um_idx (cost=0.00..285.81 rows=12433 width=0) (actual time=17.235..17.235 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
-> Index Scan using sample_time_uy_idx on sample_util_year c_9 (cost=0.57..54444.50 rows=16337 width=83) (actual time=14.964..14.964 rows=0 loops=1)
Index Cond: (channel_id = channel.channel_id)
Planning time: 1.976 ms
Execution time: 221.009 ms
So it seems that the possible solutions (without a schema change on tables) are either to drop the index and use a composite index or to use the trick mentioned by D.Rowley...
Thanks Justin and David for your help and time, I learnt quite a lot with your feedback.
Lana
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-08 08:15 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-09 12:55 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
@ 2019-01-09 16:41 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-09 19:22 ` Re: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 1 reply; 21+ messages in thread
From: David Rowley @ 2019-01-09 16:41 UTC (permalink / raw)
To: Abadie Lana <Lana.Abadie@iter.org>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, 10 Jan 2019 at 01:55, Abadie Lana <Lana.Abadie@iter.org> wrote:
> The other query suggested by D.Rowley has the same issue : still swap activity is higher.
> explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c where c.channel_id in (select channel_id from channel where name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
This is not the query I suggested. I mentioned if channel.name had a
unique index, you'd be able to do WHERE c.channel_id = (select
channel_id from channel where name = '...'). That's pretty different
to what you have above.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: select query does not pick up the right index
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-08 08:15 ` Re: select query does not pick up the right index Justin Pryzby <pryzby@telsasoft.com>
2019-01-09 12:55 ` RE: select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-09 16:41 ` Re: select query does not pick up the right index David Rowley <david.rowley@2ndquadrant.com>
@ 2019-01-09 19:22 ` Abadie Lana <Lana.Abadie@iter.org>
0 siblings, 0 replies; 21+ messages in thread
From: Abadie Lana @ 2019-01-09 19:22 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Oups wrong copy and paste. I did run your query with equal instead of in but it resulted in the same plan
________________________________
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 09 January 2019 17:41:24
To: Abadie Lana
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Thu, 10 Jan 2019 at 01:55, Abadie Lana <Lana.Abadie@iter.org> wrote:
> The other query suggested by D.Rowley has the same issue : still swap activity is higher.
> explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c where c.channel_id in (select channel_id from channel where name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5;
This is not the query I suggested. I mentioned if channel.name had a
unique index, you'd be able to do WHERE c.channel_id = (select
channel_id from channel where name = '...'). That's pretty different
to what you have above.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2019-01-09 19:22 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-02 16:28 select query does not pick up the right index Abadie Lana <Lana.Abadie@iter.org>
2019-01-02 16:45 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-03 12:58 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 00:15 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 12:57 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:01 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:13 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 13:17 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-03 13:20 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 14:34 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 21:42 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-04 08:10 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-03 23:47 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-04 08:17 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-04 08:58 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-05 04:23 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-07 16:09 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-08 08:15 ` Justin Pryzby <pryzby@telsasoft.com>
2019-01-09 12:55 ` Abadie Lana <Lana.Abadie@iter.org>
2019-01-09 16:41 ` David Rowley <david.rowley@2ndquadrant.com>
2019-01-09 19:22 ` Abadie Lana <Lana.Abadie@iter.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox