2

I'm using a Postgres database and trying to optimize the following query:

SELECT DISTINCT catalogite1_.id 
   FROM            cat_catalogitem catalogite1_ 
   INNER JOIN      cat_service service2_ 
   ON              catalogite1_.service_id=service2_.id 
   LEFT OUTER JOIN cat_entitlement_services entitledse6_ 
   ON              service2_.id=entitledse6_.service_id 
   LEFT OUTER JOIN cat_entitlement entitlemen7_ 
   ON              entitledse6_.entitlement_id=entitlemen7_.id 
   AND             (entitlemen7_.id IN ('505e03e5-7370-42c2-a26e-bdb2df593934' , 
                   '508da3b6-7147-4b16-971f-6e6476b8ef44' , 
                   '6c68fbd2-7cc4-4b7c-85c1-617b69578ab9' , 
                   '6c9e5ff0-a073-4923-a5ec-b47f5e4c120a' , 
                   '961bee54-e9d6-402c-a763-c3937b03402f' , 
                   '2f113c9a-9e2f-47d8-beda-df0e05faa167' , 
                   '471bca1e-a112-4842-bdfc-252b8848b862' , 
                   '482ba515-2197-4fdb-a74b-37d9a0795c4e' , 
                   '872038e4-766a-4b93-bf95-aa2735e7f942' , 
                   'fd6345fc-8799-42a0-83d5-0234e450e397' , 
                   '7378e830-5271-482f-b73b-7ce4232b000d' , 
                   '6aeafe3b-aac9-4c67-8895-aa77da7a7d6b')) 

       LEFT OUTER JOIN cat_subtenant catalogsub3_ 
   ON              catalogite1_.subtenant_id=catalogsub3_.id 
   LEFT OUTER JOIN cat_entitlement_catalogitems entitledca4_ 
   ON              catalogite1_.id=entitledca4_.catalogitem_id 
   LEFT OUTER JOIN cat_entitlement entitlemen5_ 
   ON              entitledca4_.entitlement_id=entitlemen5_.id 
   AND             (entitlemen5_.id IN ('505e03e5-7370-42c2-a26e-bdb2df593934' , 
                   '508da3b6-7147-4b16-971f-6e6476b8ef44' , 
                   '6c68fbd2-7cc4-4b7c-85c1-617b69578ab9' , 
                   '6c9e5ff0-a073-4923-a5ec-b47f5e4c120a' , 
                   '961bee54-e9d6-402c-a763-c3937b03402f' , 
                   '2f113c9a-9e2f-47d8-beda-df0e05faa167' , 
                   '471bca1e-a112-4842-bdfc-252b8848b862' , 
                   '482ba515-2197-4fdb-a74b-37d9a0795c4e' , 
                   '872038e4-766a-4b93-bf95-aa2735e7f942' , 
                   'fd6345fc-8799-42a0-83d5-0234e450e397' , 
                   '7378e830-5271-482f-b73b-7ce4232b000d' , 
                   '6aeafe3b-aac9-4c67-8895-aa77da7a7d6b')) 

   WHERE           catalogite1_.is_requestable=true 
   AND             catalogite1_.status='PUBLISHED' 
   AND             catalogite1_.tenant_id='intel-1' 
   AND             ( 
                                   service2_.id=NULL 
                   OR              COALESCE(NULL) IS NULL) 
   AND             service2_.status='ACTIVE' 
   AND             service2_.tenant_id='intel-1' 
   AND             ((entitlemen7_.id IS NOT NULL)
   AND             (catalogsub3_.id IS NULL
                   OR entitlemen7_.subtenant_id=catalogsub3_.id)
   AND             (entitlemen5_.id IS NULL
                   OR entitlemen5_.subtenant_id<>entitlemen7_.subtenant_id)
   OR              (entitlemen5_.id IS NOT NULL)
   AND             (catalogsub3_.id IS NULL
                   OR entitlemen5_.subtenant_id=catalogsub3_.id)
   AND             entitledca4_.is_hidden=false)

As you can see, the query is quite complex and utilizes multiple joins and filtering conditions. My issue is that this query takes over 60 seconds to complete! My target is to bring the performance down to 2-3 seconds.

I've done some background research and looked into running this query through Postgres' EXPLAIN ANALYZE feature, which gave some great insights into where the bottleneck is.

Here is a link to an easy-to-read representation of EXPLAIN ANALYZE's output:

https://explain.depesz.com/s/f3hn

As you can see, the main bottleneck seems to be at the Merge Join, where the database is filtering out over 317 million rows! Another bottle neck is the sorting that also occurs several steps before the merge join. I'm not sure why this sorting is happening because there is no ORDER BY operation in my query. The sort seems to be an external disk sort, which could be why it's proving to be so costly.

Can someone point me in the right direction to optimizing this query at this point? I think I've managed to pinpoint the main performance bottlenecks and just need a push in the right direction and/or tips on how to improve this situation.

3

I'm not sure why this sorting is happening because there is no ORDER BY operation in my query.

It is sorting so that it can do the merge join. Merge joins require sorted input.

The sort seems to be an external disk sort, which could be why it's proving to be so costly.

No, the actual sorting shouldn't take much time at all (although you might want to increase work_mem anyway, that kind of sort probably doesn't need to be on disk. What is the current setting?). Once it has the sorted data, though, it has to re-probe that data again and again as part of the merge join. That is where the time is going, and some of that time gets attributed to the sort step. Also, with this kind of plan, the overhead of collecting the times to report for an EXPLAIN ANALYZE can be huge, leading to the query taking several times longer than if it were not being monitored. If you do EXPLAIN (ANALYZE, TIMING OFF), what do you get for the bottom line execution time?

If you were to get it to use a hash join instead of a merge join, it probably would not change anything because the re-probing would still have to happen, just through a different mechanism.

The likely problem is that the query is executing as two sub-branches, one off from catalogite1_ and one off from service2_, which are then effectively cartesian joined at the end. The filtering can't be done until the end because some of the data needed for the comparisons is coming from one branch, and some from the other. And it is effectively a cartesian join because service2_ only has one qualifying row in it, meaning catalogite1_.service_id=service2_.id is not very selective

I would try changing this part of the query:

ON              service2_.id=entitledse6_.service_id

to this:

ON              catalogite1_.service_id=entitledse6_.service_id

This might allow the filtering to occur at a much lower place in the query. If this works, then it would be interesting to know why the planner didn't make this switch for you--it should be capable of it. What is your setting for join_collapse_limit?

Also, things like this:

AND             ( 
                                   service2_.id=NULL 
                   OR              COALESCE(NULL) IS NULL) 

Certainly don't help the planner make reasonable choices!

  • I tried changing the query to what you specified, but it didn't make a difference. What did make a difference was setting my work_mem to 16mb (it was at 2mb before). The query used to take 66secs before but with the work_mem change, it takes about 37secs. Which is a great improvement! Now I'm just curious... is there a better way of writing/changing my existing query that won't require me to change work_mem? I ask because I'd much prefer that route if it's an option. This is my first time dabbling in SQL query optimization, so please let me know what the best next step would be! Thanks! – NewGradDev Oct 1 '16 at 23:03
  • What does the EXPLAIN ANALYZE look like after you increase work_mem? – jjanes Oct 1 '16 at 23:22
  • Here you go: explain.depesz.com/s/AJE – NewGradDev Oct 1 '16 at 23:34
  • Sorry about that, I accidentally sent you the wrong link. Here's the right one: explain.depesz.com/s/yTOW I've been doing my own experimentation too and found that LEFT OUTER JOIN cat_entitlement_catalogitems entitledca4_ ON catalogite1_.id=entitledca4_.catalogitem_id is the operation that is taking the bulk of the time. cat_entitlement_catalogitems is a table with about 60,000 records, while the dataset that this table is being left joined with has just 10 records. Is there any way to optimize this operation? Maybe an index? – NewGradDev Oct 2 '16 at 3:46
  • When you changed the join to do catalogite1_.service_id=entitledse6_.service_id, did the plan stay the same, or did it change to a different plan which was also slow? It it changed to another slow plan, it would be interesting to see the plan for that one. If the plan didn't change, then what happens if you drop service2_ from the query altogether? – jjanes Oct 2 '16 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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