I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have
counts 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.