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.
EXPLAIN (BUFFERS, ANALYZE)
please, the rawexplain
doesn't have timing info etc.I know there is a vacuum done on the database every night.
Why is that? When using autovacuum (as is the default), you typically don't need manual vacuuming. Also: autovacuum also takes care ofANALYZE
, which you may be missing in this case? Related: Are regular VACUUM ANALYZE still recommended under 9.1?