How can I improve this query in PostgreSQL?

Question

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.

Answer 1

I immediately see a few things that confuse me here, which you should look into.

Unnecessary Join

In your query you have a portion

...  LEFT OUTER JOIN houses ON houses.id = listings.house_id ...

yet no values from the houses table are in your SELECT. The good news is that this is not affecting your performance, because the query optimizer has wisely left the houses 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

subscription_id = ### AND listing_status = 'Active'

by performing two Bitmap Index Scans, followed by a subsequent BitmapAnd. This seems like a highly inefficient way to perform this operation when the Bitmap Index Scan on listings_subscription_item_idx returns approximately 5637 rows, but the Bitmap 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 which listing_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 for listing_status = 'Active'. I'm surprised the optimizer has chosen this plan, but I have a wild guess that it's because the listing_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:

WITH listing_by_subscription AS(
SELECT * FROM "listings" 
WHERE "listings"."subscription_id" = 123)
SELECT COUNT(*) AS count_all, listing_by_subscription.id AS listings_id 
FROM listing_by_subscription
WHERE listing_by_subscription."listing_status" = 'Active'
AND EXISTS (SELECT 1 FROM apps
            WHERE apps.listing_id = listing_by_subscription.id
            AND apps.subscription_id = listing_by_subscription.subscription_id LIMIT 1)
GROUP BY listing_by_subscription.id;

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, the subscription_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 an EXPLAIN (ANALYZE,BUFFERS) to get the complete picture, but I'm wondering if this setting is sufficient.

Check you postgresql.conf file, and report back your work_mem setting, along with that EXPLAIN (ANALYZE,BUFFERS), if you're still having trouble.

Hope this works!