Re: Improving Performance of Query ~ Filter by A, Sort by B
Hello, I have tested it with release 11 and limit 20 is pushed to each partition when using index on tmstmp. Could you tell us what is the result of your query applyed to one partition EXPLAIN ANALYZE SELECT "a"."id" FROM a_partition1 "a" WHERE "a"."parent_id" IN ( 34226,24506,40987,27162 ) ORDER BY "a"."tmstmp" DESC LIMIT 20; May be that limit 20 is not pushed to partitions in your version ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Re: Improving Performance of Query ~ Filter by A, Sort by B
Thanks for looking into this!
Here's the result (I turned off the timeout and got it to finish):
EXPLAIN ANALYZE
SELECT "a"."id"
FROM a_partition1 "a"
WHERE "a"."parent_id" IN (
49188,14816,14758,8402
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;
QUERY PLAN
---
Limit (cost=0.43..5710.03 rows=20 width=12) (actual
time=1141878.105..1142350.296 rows=20 loops=1)
-> Index Scan Backward using a_tmstmp_idx1 on a_partition1 a
(cost=0.43..1662350.21 rows=5823 width=12) (actual
time=1141878.103..1142350.274 rows=20 loops=1)
Filter: (parent_id = ANY ('{49188,14816,14758,8402}'::integer[]))
Rows Removed by Filter: 7931478
Planning time: 0.122 ms
Execution time: 1142350.336 ms
(6 rows)
(Note: I've chosen parent_ids that I know are associated with the part_key
1, but the query plan was the same with the 4 parent_ids in your query.)
Looks like it's using the filter in the same way as the query on the parent
table, so seems be a problem beyond the partitioning.
And as soon as I cut it back to 3 parent_ids, jumps to a query plan
using a_parent_id_idx1
again:
EXPLAIN ANALYZE
SELECT "a"."id"
FROM a_partition1 "a"
WHERE "a"."parent_id" IN (
19948,21436,41220
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;
QUERY PLAN
Limit (cost=5004.57..5004.62 rows=20 width=12) (actual
time=36.329..36.341 rows=20 loops=1)
-> Sort (cost=5004.57..5015.49 rows=4367 width=12) (actual
time=36.328..36.332 rows=20 loops=1)
Sort Key: tmstmp DESC
Sort Method: top-N heapsort Memory: 26kB
-> Index Scan using a_parent_id_idx1 on a_partition1 a
(cost=0.43..4888.37 rows=4367 width=12) (actual time=5.581..36.270 rows=50
loops=1)
Index Cond: (parent_id = ANY
('{19948,21436,41220}'::integer[]))
Planning time: 0.117 ms
Execution time: 36.379 ms
(8 rows)
Thanks again for your help!
On Wed, Jul 11, 2018 at 5:41 PM, legrand legrand <
legrand_legr...@hotmail.com> wrote:
> Hello,
>
> I have tested it with release 11 and limit 20 is pushed to each partition
> when using index on tmstmp.
>
> Could you tell us what is the result of your query applyed to one
> partition
>
> EXPLAIN ANALYZE
> SELECT "a"."id"
> FROM a_partition1 "a"
> WHERE "a"."parent_id" IN (
> 34226,24506,40987,27162
> )
> ORDER BY "a"."tmstmp" DESC
> LIMIT 20;
>
> May be that limit 20 is not pushed to partitions in your version ?
> Regards
> PAscal
>
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
--
Lincoln Swaine-Moore
Re: Improving Performance of Query ~ Filter by A, Sort by B
Lincoln Swaine-Moore writes:
> Here's the result (I turned off the timeout and got it to finish):
> ...
I think the core of the problem here is bad rowcount estimation. We can't
tell from your output how many rows really match
> WHERE "a"."parent_id" IN (
> 49188,14816,14758,8402
> )
but the planner is guessing there are 5823 of them. In the case with
only three IN items, we have
> -> Index Scan using a_parent_id_idx1 on a_partition1 a
> (cost=0.43..4888.37 rows=4367 width=12) (actual time=5.581..36.270 rows=50
> loops=1)
>Index Cond: (parent_id = ANY
> ('{19948,21436,41220}'::integer[]))
so the planner thinks there are 4367 matching rows but there are only 50.
Anytime you've got a factor-of-100 estimation error, you're going to be
really lucky if you get a decent plan.
I suggest increasing the statistics target for the parent_id column
in hopes of getting better estimates for the number of matches.
regards, tom lane
Suggestion to optimize performance of the PLSQL procedure.
Dear expert,
Could you please review and suggest to optimize performance of the PLSQL
procedure in PostgreSQL?
I have attached the same.
Thanks in advance
Regards,
Dinesh Chandra
DISCLAIMER:
This email message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies of
the original message. Check all attachments for viruses before opening them.
All views or opinions presented in this e-mail are those of the author and may
not reflect the opinion of Cyient or those of our affiliates.
-Function to get weather forcast based on threshold value of weather
parameters
CREATE OR REPLACE FUNCTION dome.geteffectiveforcast4(
coldmaxtemp double precision,
coldmintemp double precision,
hotmaxtemp double precision,
hotmintemp double precision,
windmax double precision,
windmin double precision,
rainmax double precision,
rainmin double precision,
humiditymax double precision,
humiditymin double precision,
snowmax double precision,
snowmin double precision)
RETURNS SETOF "TABLE(lockey text, from_time timestamp without time zone,
to_time timestamp without time zone, weathertype text, flood_height text,
severity text)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red
Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by
"Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over
(partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature"<0
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over
(partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over
(partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature">=coldmintemp and "Temperature"<= coldmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Hot Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over
(partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature" >
hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" +
"Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature" between hotmintemp and hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity"
from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" >windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Wind Speed' as WeatherType,'' as "Flood_height",'' as
