I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have count
s in them, such as:
SELECT COUNT(*) AS count_all, listings.id AS listings_id
FROM "listings" LEFT OUTER JOIN houses ON houses.id = listings.house_id
WHERE "listings"."subscription_id" = 123
AND (exists (select 'x' FROM apps
WHERE apps.listing_id = listings.id
AND apps.subscription_id = listings.subscription_id LIMIT 1))
AND "listings"."listing_status" = 'Active'
GROUP BY listings.id
How can I write this query in PostgreSQL where it will return in a reasonable amount of time?
My explain plan is the following. I admit I really don't know how to read it very well. But it looks bad:
HashAggregate (cost=2579302.75..2579319.31 rows=1656 width=8)
-> Bitmap Heap Scan on listings (cost=304056.70..2579294.47 rows=1656 width=8)
Recheck Cond: ((subscription_id = 162) AND (listing_status = 'Active'::citext))
Filter: (SubPlan 1)
-> BitmapAnd (cost=304056.70..304056.70 rows=3313 width=0)
-> Bitmap Index Scan on listings_subscription_item_idx (cost=0.00..570.84 rows=5637 width=0)
Index Cond: (subscription_id = 162)
-> Bitmap Index Scan on index_listings_on_listing_status (cost=0.00..303484.78 rows=3936030 width=0)
Index Cond: (listing_status = 'Active'::citext)
SubPlan 1
-> Limit (cost=0.57..683.01 rows=1 width=0)
-> Index Scan using apps_listing_idx on apps (cost=0.57..42312.00 rows=62 width=0)
Index Cond: (listing_id = listings.id)
Filter: (subscription_id = listings.subscription_id)
This query works fantastically on a laptop with the same database, but dies in production and takes over a minute to return. Does anything jump out at anybody for things I could rapidly improve?
The database has autovacuum
turned on.
Best Answer
I immediately see a few things that confuse me here, which you should look into.
Unnecessary Join
In your query you have a portion
yet no values from the
houses
table are in yourSELECT
. The good news is that this is not affecting your performance, because the query optimizer has wisely left thehouses
table out of the query entirely. This does concern me a bit just in principle.Odd Query Plan
In your query plan, it chooses to enforce the conditions of
by performing two
Bitmap Index Scan
s, followed by a subsequentBitmapAnd
. This seems like a highly inefficient way to perform this operation when theBitmap Index Scan on listings_subscription_item_idx
returns approximately 5637 rows, but theBitmap Index Scan on index_listings_on_listing_status
returns over 3 million!Is there any chance that you have a very large number of entries in
listings
for whichlisting_status
is not'Active'
?In this case, I would think the optimal plan would be to use the
Bitmap Index Scan on listings_subscription_item_idx
to extract a set of candidate rows, and then simply filter this small set of results forlisting_status = 'Active'
. I'm surprised the optimizer has chosen this plan, but I have a wild guess that it's because thelisting_status = 'Active'
is actually highly selective on that column (such that maybe only 10% or less of the listings are Active).To try and address this, I suggest trying the following query:
Optimization Barrier in CTE
Here, I've tried to make use of the optimization barrier found when using a Common Table Expression. Any time you use the
WITH()
expression, the Postgres optimizer treats it as a separate block which it must calculate separately from the overall optimization, sort of like a 'forced materialization'. In this case, I try to use that to force the most selective predicate, thesubscription_id = ###
portion, to whittle down the result set first.Try out this query and see if it helps!
work_mem
Last but not least, there's been a lot of talk abou
work_mem
settings today. As @CraigRinger mentioned, we really need to see anEXPLAIN (ANALYZE,BUFFERS)
to get the complete picture, but I'm wondering if this setting is sufficient.Check you
postgresql.conf
file, and report back yourwork_mem
setting, along with thatEXPLAIN (ANALYZE,BUFFERS)
, if you're still having trouble.Hope this works!