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"
Best Answer
You use
UNION ALLin the most inner subquery. It produces a non-indexed record set. So I cannot find the optimization point. The only thing you can do is removeORDER BY a."createdAt" DESCfrom subquery - it will be ignored any case. - Akina