RE: select query does not pick up the right index
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 Sent: 03 January 2019 01:16 To: Abadie Lana Cc: [email protected] Subject: Re: select query does not pick up the right index On Thu, 3 Jan 2019 at 05:28, Abadie Lana 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 = ); 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
RE: select query does not pick up the right index
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 Sent: 02 January 2019 17:45 To: Abadie Lana Cc: [email protected] Subject: Re: select query does not pick up the right index On Wed, Jan 02, 2019 at 04:28:41PM +, 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
Re: select query does not pick up the right index
On Fri, 4 Jan 2019 at 01:57, Abadie Lana 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
RE: select query does not pick up the right index
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 Sent: 03 January 2019 14:01 To: Abadie Lana Cc: [email protected] Subject: Re: select query does not pick up the right index On Fri, 4 Jan 2019 at 01:57, Abadie Lana 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) Ind
Re: select query does not pick up the right index
> From: David Rowley > 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
RE: select query does not pick up the right index
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 Sent: 03 January 2019 14:18 To: Abadie Lana Cc: [email protected] Subject: Re: select query does not pick up the right index > From: David Rowley > 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
RE: select query does not pick up the right index
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: [email protected] On Behalf Of Abadie Lana Sent: 03 January 2019 14:21 To: David Rowley Cc: [email protected] Subject: [Possible Spoof] RE: select query does not pick up the right index Warning: This message was sent by [email protected] supposedly on behalf of Abadie Lana . 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 Sent: 03 January 2019 14:18 To: Abadie Lana Cc: [email protected] Subject: Re: select query does not pick up the right index > From: David Rowley > 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
Re: select query does not pick up the right index
On Fri, 4 Jan 2019 at 02:20, Abadie Lana wrote:
> > From: David Rowley
> > 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
Re: select query does not pick up the right index
On Thu, Jan 03, 2019 at 12:57:27PM +, 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;
