1

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.

3
  • EXPLAIN (BUFFERS, ANALYZE) please, the raw explain doesn't have timing info etc. Jul 8, 2015 at 4:22
  • 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 of ANALYZE, which you may be missing in this case? Related: Are regular VACUUM ANALYZE still recommended under 9.1? Jul 8, 2015 at 6:42
  • For somebody requesting answers you are remarkably impervious to requests for clarification yourself. You know the edit button right under your question? Jul 11, 2015 at 23:30

1 Answer 1

2

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!

5
  • Chris, I really appreciate your help, a lot. We are basically dead as these queries are now taking over 10K seconds and have brought ops to a halt. Here are those settings: work_mem = 64MB and maintenance_work_mem = 256MB Jul 8, 2015 at 17:37
  • 1
    1) Did you try the query I listed above? 2) Also, can you post the result of an EXPLAIN (ANALYZE,BUFFERS) in an edit to your original post? 3) Give the EXPLAIN (ANALYZE,BUFFERS) for both your original query, and the suggested query I listed above. 4) Increase your work_mem significantly, and see how the query does. In some cases, increasing work_mem is a "quick fix" which can be used until the actual problem is resolved.
    – Chris
    Jul 8, 2015 at 22:02
  • I did both. I discovered that just deleting that listing_status index sped things up an order of magnitude. Thank you for pointing that out! Jul 9, 2015 at 20:19
  • Another option which may speed up things a bit more would be to build a partial index on the listing_status for the condition where it is Active, as CREATE INDEX idx_listings_stats_active ON listings (listing_status) WHERE listing_status = 'Active';
    – Chris
    Jul 9, 2015 at 20:24
  • I'll keep that in mind: I hadn't considered a partial index. I don't really know what that is, although it seems familiar from my Oracle days years ago. Jul 9, 2015 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.