Union of two queries taking 2.4 seconds I need it in msPostgreSQL 9.2 (PostGIS) performance problemHow to...
Arrow those variables!
Can compressed videos be decoded back to their uncompresed original format?
How dangerous is XSS?
How would I stat a creature to be immune to everything but the Magic Missile spell? (just for fun)
iPad being using in wall mount battery swollen
Why is this clock signal connected to a capacitor to gnd?
What reasons are there for a Capitalist to oppose a 100% inheritance tax?
What is a romance in Latin?
What exploit are these user agents trying to use?
Mathematica command that allows it to read my intentions
What is the most common color to indicate the input-field is disabled?
What does “the session was packed” mean in this context?
How to tell a function to use the default argument values?
Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?
A friend helped me with a presentation – plagiarism or not?
Examples of smooth manifolds admitting inbetween one and a continuum of complex structures
Should I cover my bicycle overnight while bikepacking?
How can I determine if the org that I'm currently connected to is a scratch org?
How can I deal with my CEO asking me to hire someone with a higher salary than me, a co-founder?
Is there a hemisphere-neutral way of specifying a season?
How do I know where to place holes on an instrument?
How does a predictive coding aid in lossless compression?
Is there an expression that means doing something right before you will need it rather than doing it in case you might need it?
Is it possible to create a QR code using text?
Union of two queries taking 2.4 seconds I need it in ms
PostgreSQL 9.2 (PostGIS) performance problemHow to index WHERE (start_date >= '2013-12-15')How can I speed up a Postgres query containing lots of Joins with an ILIKE conditionpostgres explain plan with giant gaps between operationsHow to optimize multiple ORDER BYs?Slow fulltext search due to wildly inaccurate row estimatespostgresql 9.2 hash join issueWhy does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?Sorting killing my postgresql queryHow to make DISTINCT ON faster in PostgreSQL?
0
I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for user to not get annoyed, it is taking 2.4 seconds while I want it to be in ms, what I am doing wrong here or if query is good enough any other way to increase the time?
QUERY:
WITH nearbyfeed AS(
SELECT
'nearby' as feedType,
null as feedId,
null as feedName,
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Alerts" as a
LEFT JOIN "AlertLocations" as al
on a."alertLocationId" = al.id
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE ST_DWithin(
ST_GeogFromText('SRID=4326; POINT(13.323929 32.9062743599725)'),
al.location,
10000
)
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
), subscriptionfeed AS (
SELECT 'subscription' as feedType,
CAST(s.id AS text) as "feedId",
s.name as "feedName",
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Subscriptions" as s
LEFT JOIN "SubscriptionFeed" as sf
ON sf."subscriptionId" = s."id"
INNER JOIN "Alerts" as a
ON a."id" = sf."alertId"
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE s."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
-- AND s."id" IN ('4433caa0-4d53-11e9-bef0-2d891e682642')
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
)
SELECT counts,
un."authorDistance",
un."authorNearestDistance",
un."description",
un."totalScore",
un."upVote",
un."downVote",
un."expireVotes",
un."threatLevel",
un."userConfidence",
un."radius",
un."state",
un."ttl",
un."requiresManualExpire",
un."ttlDuringActivation",
un."ttlOverridden",
un."userId",
un."createdAt",
un."updatedAt",
un."expiredAt",
un."activatedAt",
un."feedName" as "feed.subscription.name",
un."feedId" as "feed.subscription.id",
un."feedType" as "feed.name",
un."alertTypeId" as "type.id",
un."alertTypeIcon" as "type.icon",
un."alertTypeState" as "type.state",
att."name" as "type.name",
att."description" as "type.description",
al."id" as "location.id",
al."location" as "location.location",
al."street" as "location.street",
al."landmark" as "location.landmark",
r."downVote" as "Reviews.downVote",
r."upVote" as "Reviews.upVote",
r."setExpired" as "Reviews.setExpired"
FROM (
SELECT
COUNT(*) OVER() as counts,
-- Alert Columns
(array_agg(feeds.id))[1] as id,
(array_agg(feeds."authorDistance"))[1] as "authorDistance",
(array_agg(feeds."authorNearestDistance"))[1] as "authorNearestDistance",
(array_agg(feeds."description"))[1] as "description",
(array_agg(feeds."totalScore"))[1] as "totalScore",
(array_agg(feeds."upVote"))[1] as "upVote",
(array_agg(feeds."downVote"))[1] as "downVote",
(array_agg(feeds."expireVotes"))[1] as "expireVotes",
(array_agg(feeds."threatLevel"))[1] as "threatLevel",
(array_agg(feeds."userConfidence"))[1] as "userConfidence",
(array_agg(feeds."radius"))[1] as "radius",
(array_agg(feeds."state"))[1] as "state",
(array_agg(feeds."ttl"))[1] as "ttl",
(array_agg(feeds."requiresManualExpire"))[1] as "requiresManualExpire",
(array_agg(feeds."ttlDuringActivation"))[1] as "ttlDuringActivation",
(array_agg(feeds."ttlOverridden"))[1] as "ttlOverridden",
(array_agg(feeds."userId"))[1] as "userId",
(array_agg(feeds."createdAt"))[1] as "createdAt",
(array_agg(feeds."updatedAt"))[1] as "updatedAt",
(array_agg(feeds."expiredAt"))[1] as "expiredAt",
(array_agg(feeds."activatedAt"))[1] as "activatedAt",
array_to_string(array_agg(feeds.feedType), ',') as "feedType",
array_to_string(array_agg(feeds.feedName), ',') as "feedName",
array_to_string(array_agg(feeds.feedId), ',') as "feedId",
(array_agg(feeds."alertLocationId"))[1] as "alertLocationId",
(array_agg(feeds."alertTypeId"))[1] as "alertTypeId",
(array_agg("alertTypeIcon"))[1] as "alertTypeIcon",
(array_agg("alertTypeState"))[1] as "alertTypeState"
FROM (SELECT * FROM (
SELECT * FROM nearbyfeed as nbf
UNION ALL
SELECT * FROM subscriptionfeed as sf
) as a ORDER BY a."createdAt" DESC) as feeds
GROUP BY feeds.id
LIMIT 20 OFFSET 0
) as un
INNER JOIN "AlertTypeTranslations" as att
ON un."alertTypeId" = att."alertTypeId"
AND att."langId" = 1
INNER JOIN "AlertLocations" as al
ON al."id" = un."alertLocationId"
LEFT JOIN "Reviews" as r
ON r."alertId" = un."id"
AND r."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
ORDER BY un."createdAt" DESC
Explain Plan:
"Sort (cost=168738.11..168738.16 rows=20 width=415) (actual time=2292.546..2292.548 rows=20 loops=1)"
" Sort Key: ((array_agg(feeds."createdAt"))[1]) DESC"
" Sort Method: quicksort Memory: 39kB"
" CTE nearbyfeed"
" -> Nested Loop (cost=1002.26..124449.33 rows=1 width=317) (actual time=36.500..399.780 rows=4093 loops=1)"
" Join Filter: (a_1."alertTypeId" = at.id)"
" Rows Removed by Join Filter: 147348"
" -> Nested Loop (cost=1000.42..124445.56 rows=1 width=185) (actual time=36.470..271.898 rows=4093 loops=1)"
" -> Gather (cost=1000.00..124437.10 rows=1 width=16) (actual time=36.456..229.165 rows=4093 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "AlertLocations" al_1 (cost=0.00..123437.00 rows=1 width=16) (actual time=23.157..304.493 rows=1364 loops=3)"
" Filter: ((location && '0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography) AND ('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography && _st_expand(location, '10000'::double precision)) AND _st_dwithin('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography, location, '10000'::double precision, true))"
" Rows Removed by Filter: 228636"
" -> Index Scan using idx_alerts_alert_location_id on "Alerts" a_1 (cost=0.42..8.45 rows=1 width=185) (actual time=0.008..0.008 rows=1 loops=4093)"
" Index Cond: ("alertLocationId" = al_1.id)"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash Join (cost=1.83..3.31 rows=37 width=44) (actual time=0.001..0.025 rows=37 loops=4093)"
" Hash Cond: (at.id = "AlertTypes".id)"
" -> Seq Scan on "AlertTypes" at (cost=0.00..1.37 rows=37 width=40) (actual time=0.001..0.003 rows=37 loops=4093)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.012..0.012 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" (cost=0.00..1.37 rows=37 width=4) (actual time=0.002..0.007 rows=37 loops=1)"
" CTE subscriptionfeed"
" -> Hash Join (cost=4.51..14600.16 rows=8613 width=801) (actual time=0.108..413.628 rows=99990 loops=1)"
" Hash Cond: (at_1.id = "AlertTypes_1".id)"
" -> Hash Join (cost=2.68..14530.06 rows=8613 width=757) (actual time=0.067..360.988 rows=99990 loops=1)"
" Hash Cond: (a_2."alertTypeId" = at_1.id)"
" -> Nested Loop (cost=0.84..14503.03 rows=8613 width=717) (actual time=0.041..329.670 rows=99990 loops=1)"
" -> Nested Loop (cost=0.42..1698.40 rows=8613 width=548) (actual time=0.025..27.801 rows=99990 loops=1)"
" -> Seq Scan on "Subscriptions" s (cost=0.00..1.12 rows=1 width=532) (actual time=0.005..0.024 rows=10 loops=1)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" -> Index Only Scan using subscription_feed_subscription_id_alert_id on "SubscriptionFeed" sf_1 (cost=0.42..1611.14 rows=8613 width=32) (actual time=0.006..1.691 rows=9999 loops=10)"
" Index Cond: ("subscriptionId" = s.id)"
" Heap Fetches: 0"
" -> Index Scan using "Alerts_pkey" on "Alerts" a_2 (cost=0.42..1.49 rows=1 width=185) (actual time=0.003..0.003 rows=1 loops=99990)"
" Index Cond: (id = sf_1."alertId")"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash (cost=1.37..1.37 rows=37 width=40) (actual time=0.016..0.016 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 13kB"
" -> Seq Scan on "AlertTypes" at_1 (cost=0.00..1.37 rows=37 width=40) (actual time=0.002..0.006 rows=37 loops=1)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.023..0.023 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" "AlertTypes_1" (cost=0.00..1.37 rows=37 width=4) (actual time=0.011..0.016 rows=37 loops=1)"
" -> Nested Loop Left Join (cost=2581.11..29688.19 rows=20 width=415) (actual time=2292.301..2292.518 rows=20 loops=1)"
" Join Filter: (r."alertId" = ((array_agg(feeds.id))[1]))"
" -> Nested Loop (cost=1581.11..1816.64 rows=20 width=428) (actual time=2107.385..2107.594 rows=20 loops=1)"
" -> Hash Join (cost=1580.68..1647.79 rows=20 width=358) (actual time=2107.361..2107.466 rows=20 loops=1)"
" Hash Cond: (((array_agg(feeds."alertTypeId"))[1]) = att."alertTypeId")"
" -> Limit (cost=1578.30..1644.90 rows=20 width=342) (actual time=2107.324..2107.417 rows=20 loops=1)"
" -> WindowAgg (cost=1578.30..2244.35 rows=200 width=342) (actual time=2107.323..2107.409 rows=20 loops=1)"
" -> GroupAggregate (cost=1578.30..2240.35 rows=200 width=912) (actual time=1419.039..1968.207 rows=51053 loops=1)"
" Group Key: feeds.id"
" -> Sort (cost=1578.30..1599.83 rows=8614 width=318) (actual time=1418.988..1494.844 rows=104083 loops=1)"
" Sort Key: feeds.id"
" Sort Method: external merge Disk: 37032kB"
" -> Subquery Scan on feeds (cost=907.59..1015.27 rows=8614 width=318) (actual time=1175.099..1271.317 rows=104083 loops=1)"
" -> Sort (cost=907.59..929.13 rows=8614 width=326) (actual time=1175.094..1235.404 rows=104083 loops=1)"
" Sort Key: a."createdAt" DESC"
" Sort Method: external merge Disk: 36976kB"
" -> Subquery Scan on a (cost=0.00..344.56 rows=8614 width=326) (actual time=36.506..1022.033 rows=104083 loops=1)"
" -> Append (cost=0.00..258.42 rows=8614 width=326) (actual time=36.504..998.969 rows=104083 loops=1)"
" -> CTE Scan on nearbyfeed nbf (cost=0.00..0.02 rows=1 width=326) (actual time=36.503..402.650 rows=4093 loops=1)"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..258.39 rows=8613 width=326) (actual time=0.113..585.177 rows=99990 loops=1)"
" -> CTE Scan on subscriptionfeed sf (cost=0.00..172.26 rows=8613 width=810) (actual time=0.111..552.749 rows=99990 loops=1)"
" -> Hash (cost=1.93..1.93 rows=37 width=36) (actual time=0.028..0.028 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 11kB"
" -> Seq Scan on "AlertTypeTranslations" att (cost=0.00..1.93 rows=37 width=36) (actual time=0.011..0.021 rows=37 loops=1)"
" Filter: ("langId" = 1)"
" Rows Removed by Filter: 37"
" -> Index Scan using "AlertLocations_pkey" on "AlertLocations" al (cost=0.42..8.44 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=20)"
" Index Cond: (id = ((array_agg(feeds."alertLocationId"))[1]))"
" -> Materialize (cost=1000.00..27871.25 rows=1 width=19) (actual time=9.246..9.246 rows=0 loops=20)"
" -> Gather (cost=1000.00..27871.25 rows=1 width=19) (actual time=184.910..184.942 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "Reviews" r (cost=0.00..26871.15 rows=1 width=19) (actual time=167.207..167.207 rows=0 loops=3)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" Rows Removed by Filter: 434953"
"Planning time: 2.116 ms"
"Execution time: 2313.828 ms"
query-performance optimization
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for user to not get annoyed, it is taking 2.4 seconds while I want it to be in ms, what I am doing wrong here or if query is good enough any other way to increase the time?
QUERY:
WITH nearbyfeed AS(
SELECT
'nearby' as feedType,
null as feedId,
null as feedName,
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Alerts" as a
LEFT JOIN "AlertLocations" as al
on a."alertLocationId" = al.id
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE ST_DWithin(
ST_GeogFromText('SRID=4326; POINT(13.323929 32.9062743599725)'),
al.location,
10000
)
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
), subscriptionfeed AS (
SELECT 'subscription' as feedType,
CAST(s.id AS text) as "feedId",
s.name as "feedName",
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Subscriptions" as s
LEFT JOIN "SubscriptionFeed" as sf
ON sf."subscriptionId" = s."id"
INNER JOIN "Alerts" as a
ON a."id" = sf."alertId"
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE s."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
-- AND s."id" IN ('4433caa0-4d53-11e9-bef0-2d891e682642')
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
)
SELECT counts,
un."authorDistance",
un."authorNearestDistance",
un."description",
un."totalScore",
un."upVote",
un."downVote",
un."expireVotes",
un."threatLevel",
un."userConfidence",
un."radius",
un."state",
un."ttl",
un."requiresManualExpire",
un."ttlDuringActivation",
un."ttlOverridden",
un."userId",
un."createdAt",
un."updatedAt",
un."expiredAt",
un."activatedAt",
un."feedName" as "feed.subscription.name",
un."feedId" as "feed.subscription.id",
un."feedType" as "feed.name",
un."alertTypeId" as "type.id",
un."alertTypeIcon" as "type.icon",
un."alertTypeState" as "type.state",
att."name" as "type.name",
att."description" as "type.description",
al."id" as "location.id",
al."location" as "location.location",
al."street" as "location.street",
al."landmark" as "location.landmark",
r."downVote" as "Reviews.downVote",
r."upVote" as "Reviews.upVote",
r."setExpired" as "Reviews.setExpired"
FROM (
SELECT
COUNT(*) OVER() as counts,
-- Alert Columns
(array_agg(feeds.id))[1] as id,
(array_agg(feeds."authorDistance"))[1] as "authorDistance",
(array_agg(feeds."authorNearestDistance"))[1] as "authorNearestDistance",
(array_agg(feeds."description"))[1] as "description",
(array_agg(feeds."totalScore"))[1] as "totalScore",
(array_agg(feeds."upVote"))[1] as "upVote",
(array_agg(feeds."downVote"))[1] as "downVote",
(array_agg(feeds."expireVotes"))[1] as "expireVotes",
(array_agg(feeds."threatLevel"))[1] as "threatLevel",
(array_agg(feeds."userConfidence"))[1] as "userConfidence",
(array_agg(feeds."radius"))[1] as "radius",
(array_agg(feeds."state"))[1] as "state",
(array_agg(feeds."ttl"))[1] as "ttl",
(array_agg(feeds."requiresManualExpire"))[1] as "requiresManualExpire",
(array_agg(feeds."ttlDuringActivation"))[1] as "ttlDuringActivation",
(array_agg(feeds."ttlOverridden"))[1] as "ttlOverridden",
(array_agg(feeds."userId"))[1] as "userId",
(array_agg(feeds."createdAt"))[1] as "createdAt",
(array_agg(feeds."updatedAt"))[1] as "updatedAt",
(array_agg(feeds."expiredAt"))[1] as "expiredAt",
(array_agg(feeds."activatedAt"))[1] as "activatedAt",
array_to_string(array_agg(feeds.feedType), ',') as "feedType",
array_to_string(array_agg(feeds.feedName), ',') as "feedName",
array_to_string(array_agg(feeds.feedId), ',') as "feedId",
(array_agg(feeds."alertLocationId"))[1] as "alertLocationId",
(array_agg(feeds."alertTypeId"))[1] as "alertTypeId",
(array_agg("alertTypeIcon"))[1] as "alertTypeIcon",
(array_agg("alertTypeState"))[1] as "alertTypeState"
FROM (SELECT * FROM (
SELECT * FROM nearbyfeed as nbf
UNION ALL
SELECT * FROM subscriptionfeed as sf
) as a ORDER BY a."createdAt" DESC) as feeds
GROUP BY feeds.id
LIMIT 20 OFFSET 0
) as un
INNER JOIN "AlertTypeTranslations" as att
ON un."alertTypeId" = att."alertTypeId"
AND att."langId" = 1
INNER JOIN "AlertLocations" as al
ON al."id" = un."alertLocationId"
LEFT JOIN "Reviews" as r
ON r."alertId" = un."id"
AND r."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
ORDER BY un."createdAt" DESC
Explain Plan:
"Sort (cost=168738.11..168738.16 rows=20 width=415) (actual time=2292.546..2292.548 rows=20 loops=1)"
" Sort Key: ((array_agg(feeds."createdAt"))[1]) DESC"
" Sort Method: quicksort Memory: 39kB"
" CTE nearbyfeed"
" -> Nested Loop (cost=1002.26..124449.33 rows=1 width=317) (actual time=36.500..399.780 rows=4093 loops=1)"
" Join Filter: (a_1."alertTypeId" = at.id)"
" Rows Removed by Join Filter: 147348"
" -> Nested Loop (cost=1000.42..124445.56 rows=1 width=185) (actual time=36.470..271.898 rows=4093 loops=1)"
" -> Gather (cost=1000.00..124437.10 rows=1 width=16) (actual time=36.456..229.165 rows=4093 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "AlertLocations" al_1 (cost=0.00..123437.00 rows=1 width=16) (actual time=23.157..304.493 rows=1364 loops=3)"
" Filter: ((location && '0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography) AND ('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography && _st_expand(location, '10000'::double precision)) AND _st_dwithin('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography, location, '10000'::double precision, true))"
" Rows Removed by Filter: 228636"
" -> Index Scan using idx_alerts_alert_location_id on "Alerts" a_1 (cost=0.42..8.45 rows=1 width=185) (actual time=0.008..0.008 rows=1 loops=4093)"
" Index Cond: ("alertLocationId" = al_1.id)"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash Join (cost=1.83..3.31 rows=37 width=44) (actual time=0.001..0.025 rows=37 loops=4093)"
" Hash Cond: (at.id = "AlertTypes".id)"
" -> Seq Scan on "AlertTypes" at (cost=0.00..1.37 rows=37 width=40) (actual time=0.001..0.003 rows=37 loops=4093)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.012..0.012 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" (cost=0.00..1.37 rows=37 width=4) (actual time=0.002..0.007 rows=37 loops=1)"
" CTE subscriptionfeed"
" -> Hash Join (cost=4.51..14600.16 rows=8613 width=801) (actual time=0.108..413.628 rows=99990 loops=1)"
" Hash Cond: (at_1.id = "AlertTypes_1".id)"
" -> Hash Join (cost=2.68..14530.06 rows=8613 width=757) (actual time=0.067..360.988 rows=99990 loops=1)"
" Hash Cond: (a_2."alertTypeId" = at_1.id)"
" -> Nested Loop (cost=0.84..14503.03 rows=8613 width=717) (actual time=0.041..329.670 rows=99990 loops=1)"
" -> Nested Loop (cost=0.42..1698.40 rows=8613 width=548) (actual time=0.025..27.801 rows=99990 loops=1)"
" -> Seq Scan on "Subscriptions" s (cost=0.00..1.12 rows=1 width=532) (actual time=0.005..0.024 rows=10 loops=1)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" -> Index Only Scan using subscription_feed_subscription_id_alert_id on "SubscriptionFeed" sf_1 (cost=0.42..1611.14 rows=8613 width=32) (actual time=0.006..1.691 rows=9999 loops=10)"
" Index Cond: ("subscriptionId" = s.id)"
" Heap Fetches: 0"
" -> Index Scan using "Alerts_pkey" on "Alerts" a_2 (cost=0.42..1.49 rows=1 width=185) (actual time=0.003..0.003 rows=1 loops=99990)"
" Index Cond: (id = sf_1."alertId")"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash (cost=1.37..1.37 rows=37 width=40) (actual time=0.016..0.016 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 13kB"
" -> Seq Scan on "AlertTypes" at_1 (cost=0.00..1.37 rows=37 width=40) (actual time=0.002..0.006 rows=37 loops=1)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.023..0.023 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" "AlertTypes_1" (cost=0.00..1.37 rows=37 width=4) (actual time=0.011..0.016 rows=37 loops=1)"
" -> Nested Loop Left Join (cost=2581.11..29688.19 rows=20 width=415) (actual time=2292.301..2292.518 rows=20 loops=1)"
" Join Filter: (r."alertId" = ((array_agg(feeds.id))[1]))"
" -> Nested Loop (cost=1581.11..1816.64 rows=20 width=428) (actual time=2107.385..2107.594 rows=20 loops=1)"
" -> Hash Join (cost=1580.68..1647.79 rows=20 width=358) (actual time=2107.361..2107.466 rows=20 loops=1)"
" Hash Cond: (((array_agg(feeds."alertTypeId"))[1]) = att."alertTypeId")"
" -> Limit (cost=1578.30..1644.90 rows=20 width=342) (actual time=2107.324..2107.417 rows=20 loops=1)"
" -> WindowAgg (cost=1578.30..2244.35 rows=200 width=342) (actual time=2107.323..2107.409 rows=20 loops=1)"
" -> GroupAggregate (cost=1578.30..2240.35 rows=200 width=912) (actual time=1419.039..1968.207 rows=51053 loops=1)"
" Group Key: feeds.id"
" -> Sort (cost=1578.30..1599.83 rows=8614 width=318) (actual time=1418.988..1494.844 rows=104083 loops=1)"
" Sort Key: feeds.id"
" Sort Method: external merge Disk: 37032kB"
" -> Subquery Scan on feeds (cost=907.59..1015.27 rows=8614 width=318) (actual time=1175.099..1271.317 rows=104083 loops=1)"
" -> Sort (cost=907.59..929.13 rows=8614 width=326) (actual time=1175.094..1235.404 rows=104083 loops=1)"
" Sort Key: a."createdAt" DESC"
" Sort Method: external merge Disk: 36976kB"
" -> Subquery Scan on a (cost=0.00..344.56 rows=8614 width=326) (actual time=36.506..1022.033 rows=104083 loops=1)"
" -> Append (cost=0.00..258.42 rows=8614 width=326) (actual time=36.504..998.969 rows=104083 loops=1)"
" -> CTE Scan on nearbyfeed nbf (cost=0.00..0.02 rows=1 width=326) (actual time=36.503..402.650 rows=4093 loops=1)"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..258.39 rows=8613 width=326) (actual time=0.113..585.177 rows=99990 loops=1)"
" -> CTE Scan on subscriptionfeed sf (cost=0.00..172.26 rows=8613 width=810) (actual time=0.111..552.749 rows=99990 loops=1)"
" -> Hash (cost=1.93..1.93 rows=37 width=36) (actual time=0.028..0.028 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 11kB"
" -> Seq Scan on "AlertTypeTranslations" att (cost=0.00..1.93 rows=37 width=36) (actual time=0.011..0.021 rows=37 loops=1)"
" Filter: ("langId" = 1)"
" Rows Removed by Filter: 37"
" -> Index Scan using "AlertLocations_pkey" on "AlertLocations" al (cost=0.42..8.44 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=20)"
" Index Cond: (id = ((array_agg(feeds."alertLocationId"))[1]))"
" -> Materialize (cost=1000.00..27871.25 rows=1 width=19) (actual time=9.246..9.246 rows=0 loops=20)"
" -> Gather (cost=1000.00..27871.25 rows=1 width=19) (actual time=184.910..184.942 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "Reviews" r (cost=0.00..26871.15 rows=1 width=19) (actual time=167.207..167.207 rows=0 loops=3)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" Rows Removed by Filter: 434953"
"Planning time: 2.116 ms"
"Execution time: 2313.828 ms"
query-performance optimization
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for user to not get annoyed, it is taking 2.4 seconds while I want it to be in ms, what I am doing wrong here or if query is good enough any other way to increase the time?
QUERY:
WITH nearbyfeed AS(
SELECT
'nearby' as feedType,
null as feedId,
null as feedName,
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Alerts" as a
LEFT JOIN "AlertLocations" as al
on a."alertLocationId" = al.id
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE ST_DWithin(
ST_GeogFromText('SRID=4326; POINT(13.323929 32.9062743599725)'),
al.location,
10000
)
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
), subscriptionfeed AS (
SELECT 'subscription' as feedType,
CAST(s.id AS text) as "feedId",
s.name as "feedName",
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Subscriptions" as s
LEFT JOIN "SubscriptionFeed" as sf
ON sf."subscriptionId" = s."id"
INNER JOIN "Alerts" as a
ON a."id" = sf."alertId"
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE s."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
-- AND s."id" IN ('4433caa0-4d53-11e9-bef0-2d891e682642')
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
)
SELECT counts,
un."authorDistance",
un."authorNearestDistance",
un."description",
un."totalScore",
un."upVote",
un."downVote",
un."expireVotes",
un."threatLevel",
un."userConfidence",
un."radius",
un."state",
un."ttl",
un."requiresManualExpire",
un."ttlDuringActivation",
un."ttlOverridden",
un."userId",
un."createdAt",
un."updatedAt",
un."expiredAt",
un."activatedAt",
un."feedName" as "feed.subscription.name",
un."feedId" as "feed.subscription.id",
un."feedType" as "feed.name",
un."alertTypeId" as "type.id",
un."alertTypeIcon" as "type.icon",
un."alertTypeState" as "type.state",
att."name" as "type.name",
att."description" as "type.description",
al."id" as "location.id",
al."location" as "location.location",
al."street" as "location.street",
al."landmark" as "location.landmark",
r."downVote" as "Reviews.downVote",
r."upVote" as "Reviews.upVote",
r."setExpired" as "Reviews.setExpired"
FROM (
SELECT
COUNT(*) OVER() as counts,
-- Alert Columns
(array_agg(feeds.id))[1] as id,
(array_agg(feeds."authorDistance"))[1] as "authorDistance",
(array_agg(feeds."authorNearestDistance"))[1] as "authorNearestDistance",
(array_agg(feeds."description"))[1] as "description",
(array_agg(feeds."totalScore"))[1] as "totalScore",
(array_agg(feeds."upVote"))[1] as "upVote",
(array_agg(feeds."downVote"))[1] as "downVote",
(array_agg(feeds."expireVotes"))[1] as "expireVotes",
(array_agg(feeds."threatLevel"))[1] as "threatLevel",
(array_agg(feeds."userConfidence"))[1] as "userConfidence",
(array_agg(feeds."radius"))[1] as "radius",
(array_agg(feeds."state"))[1] as "state",
(array_agg(feeds."ttl"))[1] as "ttl",
(array_agg(feeds."requiresManualExpire"))[1] as "requiresManualExpire",
(array_agg(feeds."ttlDuringActivation"))[1] as "ttlDuringActivation",
(array_agg(feeds."ttlOverridden"))[1] as "ttlOverridden",
(array_agg(feeds."userId"))[1] as "userId",
(array_agg(feeds."createdAt"))[1] as "createdAt",
(array_agg(feeds."updatedAt"))[1] as "updatedAt",
(array_agg(feeds."expiredAt"))[1] as "expiredAt",
(array_agg(feeds."activatedAt"))[1] as "activatedAt",
array_to_string(array_agg(feeds.feedType), ',') as "feedType",
array_to_string(array_agg(feeds.feedName), ',') as "feedName",
array_to_string(array_agg(feeds.feedId), ',') as "feedId",
(array_agg(feeds."alertLocationId"))[1] as "alertLocationId",
(array_agg(feeds."alertTypeId"))[1] as "alertTypeId",
(array_agg("alertTypeIcon"))[1] as "alertTypeIcon",
(array_agg("alertTypeState"))[1] as "alertTypeState"
FROM (SELECT * FROM (
SELECT * FROM nearbyfeed as nbf
UNION ALL
SELECT * FROM subscriptionfeed as sf
) as a ORDER BY a."createdAt" DESC) as feeds
GROUP BY feeds.id
LIMIT 20 OFFSET 0
) as un
INNER JOIN "AlertTypeTranslations" as att
ON un."alertTypeId" = att."alertTypeId"
AND att."langId" = 1
INNER JOIN "AlertLocations" as al
ON al."id" = un."alertLocationId"
LEFT JOIN "Reviews" as r
ON r."alertId" = un."id"
AND r."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
ORDER BY un."createdAt" DESC
Explain Plan:
"Sort (cost=168738.11..168738.16 rows=20 width=415) (actual time=2292.546..2292.548 rows=20 loops=1)"
" Sort Key: ((array_agg(feeds."createdAt"))[1]) DESC"
" Sort Method: quicksort Memory: 39kB"
" CTE nearbyfeed"
" -> Nested Loop (cost=1002.26..124449.33 rows=1 width=317) (actual time=36.500..399.780 rows=4093 loops=1)"
" Join Filter: (a_1."alertTypeId" = at.id)"
" Rows Removed by Join Filter: 147348"
" -> Nested Loop (cost=1000.42..124445.56 rows=1 width=185) (actual time=36.470..271.898 rows=4093 loops=1)"
" -> Gather (cost=1000.00..124437.10 rows=1 width=16) (actual time=36.456..229.165 rows=4093 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "AlertLocations" al_1 (cost=0.00..123437.00 rows=1 width=16) (actual time=23.157..304.493 rows=1364 loops=3)"
" Filter: ((location && '0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography) AND ('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography && _st_expand(location, '10000'::double precision)) AND _st_dwithin('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography, location, '10000'::double precision, true))"
" Rows Removed by Filter: 228636"
" -> Index Scan using idx_alerts_alert_location_id on "Alerts" a_1 (cost=0.42..8.45 rows=1 width=185) (actual time=0.008..0.008 rows=1 loops=4093)"
" Index Cond: ("alertLocationId" = al_1.id)"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash Join (cost=1.83..3.31 rows=37 width=44) (actual time=0.001..0.025 rows=37 loops=4093)"
" Hash Cond: (at.id = "AlertTypes".id)"
" -> Seq Scan on "AlertTypes" at (cost=0.00..1.37 rows=37 width=40) (actual time=0.001..0.003 rows=37 loops=4093)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.012..0.012 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" (cost=0.00..1.37 rows=37 width=4) (actual time=0.002..0.007 rows=37 loops=1)"
" CTE subscriptionfeed"
" -> Hash Join (cost=4.51..14600.16 rows=8613 width=801) (actual time=0.108..413.628 rows=99990 loops=1)"
" Hash Cond: (at_1.id = "AlertTypes_1".id)"
" -> Hash Join (cost=2.68..14530.06 rows=8613 width=757) (actual time=0.067..360.988 rows=99990 loops=1)"
" Hash Cond: (a_2."alertTypeId" = at_1.id)"
" -> Nested Loop (cost=0.84..14503.03 rows=8613 width=717) (actual time=0.041..329.670 rows=99990 loops=1)"
" -> Nested Loop (cost=0.42..1698.40 rows=8613 width=548) (actual time=0.025..27.801 rows=99990 loops=1)"
" -> Seq Scan on "Subscriptions" s (cost=0.00..1.12 rows=1 width=532) (actual time=0.005..0.024 rows=10 loops=1)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" -> Index Only Scan using subscription_feed_subscription_id_alert_id on "SubscriptionFeed" sf_1 (cost=0.42..1611.14 rows=8613 width=32) (actual time=0.006..1.691 rows=9999 loops=10)"
" Index Cond: ("subscriptionId" = s.id)"
" Heap Fetches: 0"
" -> Index Scan using "Alerts_pkey" on "Alerts" a_2 (cost=0.42..1.49 rows=1 width=185) (actual time=0.003..0.003 rows=1 loops=99990)"
" Index Cond: (id = sf_1."alertId")"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash (cost=1.37..1.37 rows=37 width=40) (actual time=0.016..0.016 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 13kB"
" -> Seq Scan on "AlertTypes" at_1 (cost=0.00..1.37 rows=37 width=40) (actual time=0.002..0.006 rows=37 loops=1)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.023..0.023 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" "AlertTypes_1" (cost=0.00..1.37 rows=37 width=4) (actual time=0.011..0.016 rows=37 loops=1)"
" -> Nested Loop Left Join (cost=2581.11..29688.19 rows=20 width=415) (actual time=2292.301..2292.518 rows=20 loops=1)"
" Join Filter: (r."alertId" = ((array_agg(feeds.id))[1]))"
" -> Nested Loop (cost=1581.11..1816.64 rows=20 width=428) (actual time=2107.385..2107.594 rows=20 loops=1)"
" -> Hash Join (cost=1580.68..1647.79 rows=20 width=358) (actual time=2107.361..2107.466 rows=20 loops=1)"
" Hash Cond: (((array_agg(feeds."alertTypeId"))[1]) = att."alertTypeId")"
" -> Limit (cost=1578.30..1644.90 rows=20 width=342) (actual time=2107.324..2107.417 rows=20 loops=1)"
" -> WindowAgg (cost=1578.30..2244.35 rows=200 width=342) (actual time=2107.323..2107.409 rows=20 loops=1)"
" -> GroupAggregate (cost=1578.30..2240.35 rows=200 width=912) (actual time=1419.039..1968.207 rows=51053 loops=1)"
" Group Key: feeds.id"
" -> Sort (cost=1578.30..1599.83 rows=8614 width=318) (actual time=1418.988..1494.844 rows=104083 loops=1)"
" Sort Key: feeds.id"
" Sort Method: external merge Disk: 37032kB"
" -> Subquery Scan on feeds (cost=907.59..1015.27 rows=8614 width=318) (actual time=1175.099..1271.317 rows=104083 loops=1)"
" -> Sort (cost=907.59..929.13 rows=8614 width=326) (actual time=1175.094..1235.404 rows=104083 loops=1)"
" Sort Key: a."createdAt" DESC"
" Sort Method: external merge Disk: 36976kB"
" -> Subquery Scan on a (cost=0.00..344.56 rows=8614 width=326) (actual time=36.506..1022.033 rows=104083 loops=1)"
" -> Append (cost=0.00..258.42 rows=8614 width=326) (actual time=36.504..998.969 rows=104083 loops=1)"
" -> CTE Scan on nearbyfeed nbf (cost=0.00..0.02 rows=1 width=326) (actual time=36.503..402.650 rows=4093 loops=1)"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..258.39 rows=8613 width=326) (actual time=0.113..585.177 rows=99990 loops=1)"
" -> CTE Scan on subscriptionfeed sf (cost=0.00..172.26 rows=8613 width=810) (actual time=0.111..552.749 rows=99990 loops=1)"
" -> Hash (cost=1.93..1.93 rows=37 width=36) (actual time=0.028..0.028 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 11kB"
" -> Seq Scan on "AlertTypeTranslations" att (cost=0.00..1.93 rows=37 width=36) (actual time=0.011..0.021 rows=37 loops=1)"
" Filter: ("langId" = 1)"
" Rows Removed by Filter: 37"
" -> Index Scan using "AlertLocations_pkey" on "AlertLocations" al (cost=0.42..8.44 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=20)"
" Index Cond: (id = ((array_agg(feeds."alertLocationId"))[1]))"
" -> Materialize (cost=1000.00..27871.25 rows=1 width=19) (actual time=9.246..9.246 rows=0 loops=20)"
" -> Gather (cost=1000.00..27871.25 rows=1 width=19) (actual time=184.910..184.942 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "Reviews" r (cost=0.00..26871.15 rows=1 width=19) (actual time=167.207..167.207 rows=0 loops=3)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" Rows Removed by Filter: 434953"
"Planning time: 2.116 ms"
"Execution time: 2313.828 ms"
query-performance optimization
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for user to not get annoyed, it is taking 2.4 seconds while I want it to be in ms, what I am doing wrong here or if query is good enough any other way to increase the time?
QUERY:
WITH nearbyfeed AS(
SELECT
'nearby' as feedType,
null as feedId,
null as feedName,
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Alerts" as a
LEFT JOIN "AlertLocations" as al
on a."alertLocationId" = al.id
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE ST_DWithin(
ST_GeogFromText('SRID=4326; POINT(13.323929 32.9062743599725)'),
al.location,
10000
)
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
), subscriptionfeed AS (
SELECT 'subscription' as feedType,
CAST(s.id AS text) as "feedId",
s.name as "feedName",
at."icon" as "alertTypeIcon",
at."state" as "alertTypeState",
a.*
FROM "Subscriptions" as s
LEFT JOIN "SubscriptionFeed" as sf
ON sf."subscriptionId" = s."id"
INNER JOIN "Alerts" as a
ON a."id" = sf."alertId"
LEFT JOIN "AlertTypes" as at
ON at.id = a."alertTypeId"
WHERE s."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
-- AND s."id" IN ('4433caa0-4d53-11e9-bef0-2d891e682642')
AND a."createdAt" > '2018-03-29 15:20:13.568452+05'
AND a."createdAt" < '2019-09-01 19:00:10'
AND at.id IN (SELECT id FROM "AlertTypes")
)
SELECT counts,
un."authorDistance",
un."authorNearestDistance",
un."description",
un."totalScore",
un."upVote",
un."downVote",
un."expireVotes",
un."threatLevel",
un."userConfidence",
un."radius",
un."state",
un."ttl",
un."requiresManualExpire",
un."ttlDuringActivation",
un."ttlOverridden",
un."userId",
un."createdAt",
un."updatedAt",
un."expiredAt",
un."activatedAt",
un."feedName" as "feed.subscription.name",
un."feedId" as "feed.subscription.id",
un."feedType" as "feed.name",
un."alertTypeId" as "type.id",
un."alertTypeIcon" as "type.icon",
un."alertTypeState" as "type.state",
att."name" as "type.name",
att."description" as "type.description",
al."id" as "location.id",
al."location" as "location.location",
al."street" as "location.street",
al."landmark" as "location.landmark",
r."downVote" as "Reviews.downVote",
r."upVote" as "Reviews.upVote",
r."setExpired" as "Reviews.setExpired"
FROM (
SELECT
COUNT(*) OVER() as counts,
-- Alert Columns
(array_agg(feeds.id))[1] as id,
(array_agg(feeds."authorDistance"))[1] as "authorDistance",
(array_agg(feeds."authorNearestDistance"))[1] as "authorNearestDistance",
(array_agg(feeds."description"))[1] as "description",
(array_agg(feeds."totalScore"))[1] as "totalScore",
(array_agg(feeds."upVote"))[1] as "upVote",
(array_agg(feeds."downVote"))[1] as "downVote",
(array_agg(feeds."expireVotes"))[1] as "expireVotes",
(array_agg(feeds."threatLevel"))[1] as "threatLevel",
(array_agg(feeds."userConfidence"))[1] as "userConfidence",
(array_agg(feeds."radius"))[1] as "radius",
(array_agg(feeds."state"))[1] as "state",
(array_agg(feeds."ttl"))[1] as "ttl",
(array_agg(feeds."requiresManualExpire"))[1] as "requiresManualExpire",
(array_agg(feeds."ttlDuringActivation"))[1] as "ttlDuringActivation",
(array_agg(feeds."ttlOverridden"))[1] as "ttlOverridden",
(array_agg(feeds."userId"))[1] as "userId",
(array_agg(feeds."createdAt"))[1] as "createdAt",
(array_agg(feeds."updatedAt"))[1] as "updatedAt",
(array_agg(feeds."expiredAt"))[1] as "expiredAt",
(array_agg(feeds."activatedAt"))[1] as "activatedAt",
array_to_string(array_agg(feeds.feedType), ',') as "feedType",
array_to_string(array_agg(feeds.feedName), ',') as "feedName",
array_to_string(array_agg(feeds.feedId), ',') as "feedId",
(array_agg(feeds."alertLocationId"))[1] as "alertLocationId",
(array_agg(feeds."alertTypeId"))[1] as "alertTypeId",
(array_agg("alertTypeIcon"))[1] as "alertTypeIcon",
(array_agg("alertTypeState"))[1] as "alertTypeState"
FROM (SELECT * FROM (
SELECT * FROM nearbyfeed as nbf
UNION ALL
SELECT * FROM subscriptionfeed as sf
) as a ORDER BY a."createdAt" DESC) as feeds
GROUP BY feeds.id
LIMIT 20 OFFSET 0
) as un
INNER JOIN "AlertTypeTranslations" as att
ON un."alertTypeId" = att."alertTypeId"
AND att."langId" = 1
INNER JOIN "AlertLocations" as al
ON al."id" = un."alertLocationId"
LEFT JOIN "Reviews" as r
ON r."alertId" = un."id"
AND r."userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'
ORDER BY un."createdAt" DESC
Explain Plan:
"Sort (cost=168738.11..168738.16 rows=20 width=415) (actual time=2292.546..2292.548 rows=20 loops=1)"
" Sort Key: ((array_agg(feeds."createdAt"))[1]) DESC"
" Sort Method: quicksort Memory: 39kB"
" CTE nearbyfeed"
" -> Nested Loop (cost=1002.26..124449.33 rows=1 width=317) (actual time=36.500..399.780 rows=4093 loops=1)"
" Join Filter: (a_1."alertTypeId" = at.id)"
" Rows Removed by Join Filter: 147348"
" -> Nested Loop (cost=1000.42..124445.56 rows=1 width=185) (actual time=36.470..271.898 rows=4093 loops=1)"
" -> Gather (cost=1000.00..124437.10 rows=1 width=16) (actual time=36.456..229.165 rows=4093 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "AlertLocations" al_1 (cost=0.00..123437.00 rows=1 width=16) (actual time=23.157..304.493 rows=1364 loops=3)"
" Filter: ((location && '0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography) AND ('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography && _st_expand(location, '10000'::double precision)) AND _st_dwithin('0101000020E6100000B4739A05DAA52A4082A458CC00744040'::geography, location, '10000'::double precision, true))"
" Rows Removed by Filter: 228636"
" -> Index Scan using idx_alerts_alert_location_id on "Alerts" a_1 (cost=0.42..8.45 rows=1 width=185) (actual time=0.008..0.008 rows=1 loops=4093)"
" Index Cond: ("alertLocationId" = al_1.id)"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash Join (cost=1.83..3.31 rows=37 width=44) (actual time=0.001..0.025 rows=37 loops=4093)"
" Hash Cond: (at.id = "AlertTypes".id)"
" -> Seq Scan on "AlertTypes" at (cost=0.00..1.37 rows=37 width=40) (actual time=0.001..0.003 rows=37 loops=4093)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.012..0.012 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" (cost=0.00..1.37 rows=37 width=4) (actual time=0.002..0.007 rows=37 loops=1)"
" CTE subscriptionfeed"
" -> Hash Join (cost=4.51..14600.16 rows=8613 width=801) (actual time=0.108..413.628 rows=99990 loops=1)"
" Hash Cond: (at_1.id = "AlertTypes_1".id)"
" -> Hash Join (cost=2.68..14530.06 rows=8613 width=757) (actual time=0.067..360.988 rows=99990 loops=1)"
" Hash Cond: (a_2."alertTypeId" = at_1.id)"
" -> Nested Loop (cost=0.84..14503.03 rows=8613 width=717) (actual time=0.041..329.670 rows=99990 loops=1)"
" -> Nested Loop (cost=0.42..1698.40 rows=8613 width=548) (actual time=0.025..27.801 rows=99990 loops=1)"
" -> Seq Scan on "Subscriptions" s (cost=0.00..1.12 rows=1 width=532) (actual time=0.005..0.024 rows=10 loops=1)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" -> Index Only Scan using subscription_feed_subscription_id_alert_id on "SubscriptionFeed" sf_1 (cost=0.42..1611.14 rows=8613 width=32) (actual time=0.006..1.691 rows=9999 loops=10)"
" Index Cond: ("subscriptionId" = s.id)"
" Heap Fetches: 0"
" -> Index Scan using "Alerts_pkey" on "Alerts" a_2 (cost=0.42..1.49 rows=1 width=185) (actual time=0.003..0.003 rows=1 loops=99990)"
" Index Cond: (id = sf_1."alertId")"
" Filter: (("createdAt" > '2018-03-29 10:20:13.568452+00'::timestamp with time zone) AND ("createdAt" < '2019-09-01 19:00:10+00'::timestamp with time zone))"
" -> Hash (cost=1.37..1.37 rows=37 width=40) (actual time=0.016..0.016 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 13kB"
" -> Seq Scan on "AlertTypes" at_1 (cost=0.00..1.37 rows=37 width=40) (actual time=0.002..0.006 rows=37 loops=1)"
" -> Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.023..0.023 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on "AlertTypes" "AlertTypes_1" (cost=0.00..1.37 rows=37 width=4) (actual time=0.011..0.016 rows=37 loops=1)"
" -> Nested Loop Left Join (cost=2581.11..29688.19 rows=20 width=415) (actual time=2292.301..2292.518 rows=20 loops=1)"
" Join Filter: (r."alertId" = ((array_agg(feeds.id))[1]))"
" -> Nested Loop (cost=1581.11..1816.64 rows=20 width=428) (actual time=2107.385..2107.594 rows=20 loops=1)"
" -> Hash Join (cost=1580.68..1647.79 rows=20 width=358) (actual time=2107.361..2107.466 rows=20 loops=1)"
" Hash Cond: (((array_agg(feeds."alertTypeId"))[1]) = att."alertTypeId")"
" -> Limit (cost=1578.30..1644.90 rows=20 width=342) (actual time=2107.324..2107.417 rows=20 loops=1)"
" -> WindowAgg (cost=1578.30..2244.35 rows=200 width=342) (actual time=2107.323..2107.409 rows=20 loops=1)"
" -> GroupAggregate (cost=1578.30..2240.35 rows=200 width=912) (actual time=1419.039..1968.207 rows=51053 loops=1)"
" Group Key: feeds.id"
" -> Sort (cost=1578.30..1599.83 rows=8614 width=318) (actual time=1418.988..1494.844 rows=104083 loops=1)"
" Sort Key: feeds.id"
" Sort Method: external merge Disk: 37032kB"
" -> Subquery Scan on feeds (cost=907.59..1015.27 rows=8614 width=318) (actual time=1175.099..1271.317 rows=104083 loops=1)"
" -> Sort (cost=907.59..929.13 rows=8614 width=326) (actual time=1175.094..1235.404 rows=104083 loops=1)"
" Sort Key: a."createdAt" DESC"
" Sort Method: external merge Disk: 36976kB"
" -> Subquery Scan on a (cost=0.00..344.56 rows=8614 width=326) (actual time=36.506..1022.033 rows=104083 loops=1)"
" -> Append (cost=0.00..258.42 rows=8614 width=326) (actual time=36.504..998.969 rows=104083 loops=1)"
" -> CTE Scan on nearbyfeed nbf (cost=0.00..0.02 rows=1 width=326) (actual time=36.503..402.650 rows=4093 loops=1)"
" -> Subquery Scan on "*SELECT* 2" (cost=0.00..258.39 rows=8613 width=326) (actual time=0.113..585.177 rows=99990 loops=1)"
" -> CTE Scan on subscriptionfeed sf (cost=0.00..172.26 rows=8613 width=810) (actual time=0.111..552.749 rows=99990 loops=1)"
" -> Hash (cost=1.93..1.93 rows=37 width=36) (actual time=0.028..0.028 rows=37 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 11kB"
" -> Seq Scan on "AlertTypeTranslations" att (cost=0.00..1.93 rows=37 width=36) (actual time=0.011..0.021 rows=37 loops=1)"
" Filter: ("langId" = 1)"
" Rows Removed by Filter: 37"
" -> Index Scan using "AlertLocations_pkey" on "AlertLocations" al (cost=0.42..8.44 rows=1 width=86) (actual time=0.005..0.005 rows=1 loops=20)"
" Index Cond: (id = ((array_agg(feeds."alertLocationId"))[1]))"
" -> Materialize (cost=1000.00..27871.25 rows=1 width=19) (actual time=9.246..9.246 rows=0 loops=20)"
" -> Gather (cost=1000.00..27871.25 rows=1 width=19) (actual time=184.910..184.942 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on "Reviews" r (cost=0.00..26871.15 rows=1 width=19) (actual time=167.207..167.207 rows=0 loops=3)"
" Filter: ("userId" = '60071240-5051-11e9-afc7-5b506e49c2bc'::uuid)"
" Rows Removed by Filter: 434953"
"Planning time: 2.116 ms"
"Execution time: 2313.828 ms"
query-performance optimization
query-performance optimization
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MuhammadMuhammad
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MuhammadMuhammad
1011
asked 8 mins ago
MuhammadMuhammad
1011
1011
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Muhammad is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Muhammad is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233912%2funion-of-two-queries-taking-2-4-seconds-i-need-it-in-ms%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Muhammad is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Muhammad is a new contributor. Be nice, and check out our Code of Conduct.
Muhammad is a new contributor. Be nice, and check out our Code of Conduct.
Muhammad is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233912%2funion-of-two-queries-taking-2-4-seconds-i-need-it-in-ms%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
