Why is my big Postgres query faster if I separate it into 3 smaller queries?

Question

I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.

However, if I break my big query down into 3 smaller queries linked together by my application code, the overall execution time is much faster.

Why is that? I thought that using one big query was better in general, because it gave the Postgres query planner full control to optimize, and network database calls are reduced to one call only. But it looks like I may be wrong in some cases.

Here are the full details.

Big query

Query:

SELECT users.*
FROM users
WHERE (users.id IN
         (SELECT users.id
          FROM users
          INNER JOIN company_users ON users.id = company_users.user_id
          INNER JOIN bag_contributors ON bag_contributors.user_id = users.id
          INNER JOIN bags ON bags.deleted = FALSE
          AND bags.id = bag_contributors.bag_id
          INNER JOIN bag_items ON bag_items.deleted = FALSE
          AND bag_items.bag_id = bags.id
          WHERE company_users.deleted_at IS NULL
            AND company_users.company_id = 1595
            AND bag_items.item_id IN
              (SELECT items.id
               FROM items
               WHERE items.slug IN ('gray-hat', 'usb-cable')))
       OR users.id IN
         (SELECT users.id
          FROM users
          INNER JOIN company_users ON users.id = company_users.user_id
          INNER JOIN bags ON bags.deleted = FALSE
          AND bags.user_type = 'User'
          AND bags.user_id = users.id
          INNER JOIN bag_items ON bag_items.deleted = FALSE
          AND bag_items.bag_id = bags.id
          WHERE company_users.deleted_at IS NULL
            AND company_users.company_id = 1595
            AND bag_items.item_id IN
              (SELECT items.id
               FROM items
               WHERE items.slug IN ('gray-hat', 'usb-cable'))))
ORDER BY users.pending_user ASC

Explain analyze result:

Index Scan using index_users_on_pending_user on users  (cost=1293.90..286963.01 rows=941157 width=2029) (actual time=7.880..1062.384 rows=12 loops=1)
  Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
  Rows Removed by Filter: 1257498
  SubPlan 1
    ->  Nested Loop  (cost=2.54..573.48 rows=1 width=4) (actual time=0.301..5.760 rows=32 loops=1)
          Join Filter: (bag_items.item_id = items.id)
          Rows Removed by Join Filter: 4078
          ->  Nested Loop  (cost=2.13..566.53 rows=56 width=8) (actual time=0.200..3.965 rows=4110 loops=1)
                ->  Nested Loop  (cost=1.70..550.87 rows=6 width=16) (actual time=0.189..0.680 rows=63 loops=1)
                      ->  Nested Loop  (cost=1.27..542.72 rows=6 width=12) (actual time=0.147..0.400 rows=70 loops=1)
                            ->  Nested Loop  (cost=0.85..432.16 rows=154 width=12) (actual time=0.027..0.227 rows=28 loops=1)
                                  ->  Index Scan using index_company_users_on_company_id_and_deleted_at on company_users  (cost=0.43..173.33 rows=154 width=8) (actual time=0.016..0.048 rows=28 loops=1)
                                        Index Cond: ((company_id = 1595) AND (deleted_at IS NULL))
                                  ->  Index Only Scan using users_pkey on users users_1  (cost=0.43..1.68 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=28)
                                        Index Cond: (id = company_users.user_id)
                                        Heap Fetches: 19
                            ->  Index Only Scan using index_bag_contributors_on_user_id_and_bag_id on bag_contributors  (cost=0.41..0.61 rows=11 width=16) (actual time=0.004..0.005 rows=2 loops=28)
                                  Index Cond: (user_id = users_1.id)
                                  Heap Fetches: 59
                      ->  Index Scan using bags_pkey on bags  (cost=0.42..1.36 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=70)
                            Index Cond: (id = bag_contributors.bag_id)
                            Filter: (NOT deleted)
                            Rows Removed by Filter: 0
                ->  Index Scan using index_bag_items_on_bag_id on bag_items  (cost=0.43..1.93 rows=68 width=8) (actual time=0.004..0.040 rows=65 loops=63)
                      Index Cond: (bag_id = bags.id)
                      Filter: (NOT deleted)
                      Rows Removed by Filter: 2
          ->  Materialize  (cost=0.41..5.27 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=4110)
                ->  Index Scan using index_items_on_slug on items  (cost=0.41..5.26 rows=2 width=4) (actual time=0.048..0.077 rows=1 loops=1)
                      Index Cond: (slug = ANY ('{gray-hat,jeans}'::citext[]))
  SubPlan 2
    ->  Hash Join  (cost=7.00..719.99 rows=1 width=4) (actual time=1.119..1.703 rows=2 loops=1)
          Hash Cond: (bag_items_1.item_id = items_1.id)
          ->  Nested Loop  (cost=1.71..712.93 rows=676 width=8) (actual time=0.070..1.538 rows=766 loops=1)
                ->  Nested Loop  (cost=1.28..519.52 rows=72 width=8) (actual time=0.060..0.525 rows=52 loops=1)
                      ->  Nested Loop  (cost=0.85..432.16 rows=154 width=12) (actual time=0.046..0.236 rows=28 loops=1)
                            ->  Index Scan using index_company_users_on_company_id_and_deleted_at on company_users company_users_1  (cost=0.43..173.33 rows=154 width=8) (actual time=0.013..0.042 rows=28 loops=1)
                                  Index Cond: ((company_id = 1595) AND (deleted_at IS NULL))
                            ->  Index Only Scan using users_pkey on users users_2  (cost=0.43..1.68 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=28)
                                  Index Cond: (id = company_users_1.user_id)
                                  Heap Fetches: 19
                      ->  Index Scan using index_bags_on_user_id on bags bags_1  (cost=0.42..0.55 rows=2 width=8) (actual time=0.007..0.009 rows=2 loops=28)
                            Index Cond: (user_id = users_2.id)
                            Filter: ((NOT deleted) AND ((user_type)::text = 'User'::text))
                            Rows Removed by Filter: 0
                ->  Index Scan using index_bag_items_on_bag_id on bag_items bag_items_1  (cost=0.43..2.01 rows=68 width=8) (actual time=0.008..0.017 rows=15 loops=52)
                      Index Cond: (bag_id = bags_1.id)
                      Filter: (NOT deleted)
                      Rows Removed by Filter: 9
          ->  Hash  (cost=5.26..5.26 rows=2 width=4) (actual time=0.072..0.073 rows=1 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                ->  Index Scan using index_items_on_slug on items items_1  (cost=0.41..5.26 rows=2 width=4) (actual time=0.041..0.069 rows=1 loops=1)
                      Index Cond: (slug = ANY ('{gray-hat,jeans}'::citext[]))
Planning Time: 6.049 ms
Execution Time: 1062.577 ms

Small queries

There are 3 small queries in total. The application code uses the first 2 queries to load IDs in memory, and the 3rd query to load users that have those IDs.

Queries:

SELECT users.id
FROM users
INNER JOIN company_users ON users.id = company_users.user_id
INNER JOIN bag_contributors ON bag_contributors.user_id = users.id
INNER JOIN bags ON bags.deleted = FALSE
AND bags.id = bag_contributors.bag_id
INNER JOIN bag_items ON bag_items.deleted = FALSE
AND bag_items.bag_id = bags.id
WHERE company_users.deleted_at IS NULL
  AND company_users.company_id = 1595
  AND bag_items.item_id IN
    (SELECT items.id
     FROM items
     WHERE items.slug IN ('gray-hat', 'jeans'))


---------------------------------------------------------------------

SELECT users.id
FROM users
INNER JOIN company_users ON users.id = company_users.user_id
INNER JOIN bags ON bags.deleted = FALSE
AND bags.user_type = 'User'
AND bags.user_id = users.id
INNER JOIN bag_items ON bag_items.deleted = FALSE
AND bag_items.bag_id = bags.id
WHERE company_users.deleted_at IS NULL
  AND company_users.company_id = 1595
  AND bag_items.item_id IN
    (SELECT items.id
     FROM items
     WHERE items.slug IN ('gray-hat', 'jeans'))


---------------------------------------------------------------------

SELECT users.*
FROM users
WHERE (users.id IN (2, 2, 2, 2, 234196, 234196, 234196, 234196, 234196, 234196, 234196, 234711, 605774, 648863, 912565, 912565, 1123517, 1123517, 1123517, 1097143, 1097143, 1097143, 257253, 257253, 257253, 257253, 257253, 257253, 257253, 1026609, 968371, 1107724)
       OR users.id IN (2, 257253))
ORDER BY users.pending_user ASC

Explain analyse results:

Nested Loop  (cost=2.54..573.48 rows=1 width=4) (actual time=18.414..35.196 rows=32 loops=1)
  Join Filter: (bag_items.item_id = items.id)
  Rows Removed by Join Filter: 4078
  ->  Nested Loop  (cost=2.13..566.53 rows=56 width=8) (actual time=17.887..33.104 rows=4110 loops=1)
        ->  Nested Loop  (cost=1.70..550.87 rows=6 width=16) (actual time=17.093..22.972 rows=63 loops=1)
              ->  Nested Loop  (cost=1.27..542.72 rows=6 width=12) (actual time=17.015..22.613 rows=70 loops=1)
                    ->  Nested Loop  (cost=0.85..432.16 rows=154 width=12) (actual time=9.570..19.868 rows=28 loops=1)
                          ->  Index Scan using index_company_users_on_company_id_and_deleted_at on company_users  (cost=0.43..173.33 rows=154 width=8) (actual time=9.379..18.490 rows=28 loops=1)
                                Index Cond: ((company_id = 1595) AND (deleted_at IS NULL))
                          ->  Index Only Scan using users_pkey on users  (cost=0.43..1.68 rows=1 width=4) (actual time=0.047..0.047 rows=1 loops=28)
                                Index Cond: (id = company_users.user_id)
                                Heap Fetches: 19
                    ->  Index Only Scan using index_bag_contributors_on_user_id_and_bag_id on bag_contributors  (cost=0.41..0.61 rows=11 width=16) (actual time=0.045..0.097 rows=2 loops=28)
                          Index Cond: (user_id = users.id)
                          Heap Fetches: 67
              ->  Index Scan using bags_pkey on bags  (cost=0.42..1.36 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=70)
                    Index Cond: (id = bag_contributors.bag_id)
                    Filter: (NOT deleted)
                    Rows Removed by Filter: 0
        ->  Index Scan using index_bag_items_on_bag_id on bag_items  (cost=0.43..1.93 rows=68 width=8) (actual time=0.083..0.149 rows=65 loops=63)
              Index Cond: (bag_id = bags.id)
              Filter: (NOT deleted)
              Rows Removed by Filter: 2
  ->  Materialize  (cost=0.41..5.27 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=4110)
        ->  Index Scan using index_items_on_slug on items  (cost=0.41..5.26 rows=2 width=4) (actual time=0.466..0.495 rows=1 loops=1)
              Index Cond: (slug = ANY ('{gray-hat,jeans}'::citext[]))
Planning Time: 5.734 ms
Execution Time: 35.722 ms

----------------------------------------------------------------------------------------------------------------------------

Hash Join  (cost=7.00..719.99 rows=1 width=4) (actual time=31.624..62.787 rows=2 loops=1)
  Hash Cond: (bag_items.item_id = items.id)
  ->  Nested Loop  (cost=1.71..712.93 rows=676 width=8) (actual time=0.046..62.539 rows=766 loops=1)
        ->  Nested Loop  (cost=1.28..519.52 rows=72 width=8) (actual time=0.038..23.533 rows=52 loops=1)
              ->  Nested Loop  (cost=0.85..432.16 rows=154 width=12) (actual time=0.026..0.460 rows=28 loops=1)
                    ->  Index Scan using index_company_users_on_company_id_and_deleted_at on company_users  (cost=0.43..173.33 rows=154 width=8) (actual time=0.014..0.091 rows=28 loops=1)
                          Index Cond: ((company_id = 1595) AND (deleted_at IS NULL))
                    ->  Index Only Scan using users_pkey on users  (cost=0.43..1.68 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=28)
                          Index Cond: (id = company_users.user_id)
                          Heap Fetches: 19
              ->  Index Scan using index_bags_on_user_id on bags  (cost=0.42..0.55 rows=2 width=8) (actual time=0.816..0.822 rows=2 loops=28)
                    Index Cond: (user_id = users.id)
                    Filter: ((NOT deleted) AND ((user_type)::text = 'User'::text))
                    Rows Removed by Filter: 0
        ->  Index Scan using index_bag_items_on_bag_id on bag_items  (cost=0.43..2.01 rows=68 width=8) (actual time=0.590..0.745 rows=15 loops=52)
              Index Cond: (bag_id = bags.id)
              Filter: (NOT deleted)
              Rows Removed by Filter: 9
  ->  Hash  (cost=5.26..5.26 rows=2 width=4) (actual time=0.086..0.087 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Index Scan using index_items_on_slug on items  (cost=0.41..5.26 rows=2 width=4) (actual time=0.045..0.083 rows=1 loops=1)
              Index Cond: (slug = ANY ('{gray-hat,jeans}'::citext[]))
Planning Time: 1.560 ms
Execution Time: 62.878 ms

----------------------------------------------------------------------------------------------------------------------------

Sort  (cost=92.21..92.30 rows=34 width=2029) (actual time=0.291..0.294 rows=12 loops=1)
  Sort Key: pending_user
  Sort Method: quicksort  Memory: 37kB
  ->  Bitmap Heap Scan on users  (cost=52.21..91.35 rows=34 width=2029) (actual time=0.090..0.230 rows=12 loops=1)
        Recheck Cond: ((id = ANY ('{2,2,2,2,234196,234196,234196,234196,234196,234196,234196,234711,605774,648863,912565,912565,1123517,1123517,1123517,1097143,1097143,1097143,257253,257253,257253,257253,257253,257253,257253,1026609,968371,1107724}'::integer[])) OR (id = ANY ('{2,257253}'::integer[])))
        Heap Blocks: exact=12
        ->  BitmapOr  (cost=52.21..52.21 rows=34 width=0) (actual time=0.078..0.079 rows=0 loops=1)
              ->  Bitmap Index Scan on users_pkey  (cost=0.00..49.12 rows=32 width=0) (actual time=0.074..0.074 rows=13 loops=1)
                    Index Cond: (id = ANY ('{2,2,2,2,234196,234196,234196,234196,234196,234196,234196,234711,605774,648863,912565,912565,1123517,1123517,1123517,1097143,1097143,1097143,257253,257253,257253,257253,257253,257253,257253,1026609,968371,1107724}'::integer[]))
              ->  Bitmap Index Scan on users_pkey  (cost=0.00..3.07 rows=2 width=0) (actual time=0.004..0.004 rows=3 loops=1)
                    Index Cond: (id = ANY ('{2,257253}'::integer[]))
Planning Time: 1.106 ms
Execution Time: 0.448 ms

---

The big query takes about 1000 ms, whereas the 3 small queries altogether take about 100 ms! And that includes the network back and forths between the application and the database. What boggles me is that the 2 first small queries have just been extracted from the big one, with an added 3rd query to combine both, so the overall complexity is basically the same.

Any idea why this is happening? I am thinking of improving the performance of other similar slow big queries by breaking them down into smaller queries.

Answer 1

I thought that using one big query was better in general, because it gave the Postgres query planner full control to optimize,

Unfortunately, it also gives the planner a whole world of mistakes it could possibly make. It has to make the plans based on the row estimates available to it (what else could it do?) and the estimates available are off by over 70,000 fold. So it is quite unsurprising that it picks a bad plan. Although in this case, it is probably incapable of coming up with a good plan anyway, because of the convoluted nature of the query. Combining the two branches of the OR without getting duplicates would generally use a bitmap, but in this case you would need an indirect bitmap, which PostgreSQL does not implement. But ideas to improve the software are better suited to the developer's mailing list, not to this forum.

When you break it down into smaller queries, though, you do so along the lines which match your intuition. You should know you data better than anyone else does. The planner doesn't have access to your intuition, though.

Answer 2

Try to rewrite the query as follows:

SELECT users.*
FROM users
WHERE users.id = ANY (array(SELECT /* first subquery */) ||
                      array(SELECT /* second subquery */));
ORDER BY users.pending_user;

That should force an execution plan similar to the three individual queries.

Answer 3

The main problem of the combined query materializes here (formatted for clarity):

Index Scan using index_users_on_pending_user on users  
   (cost=1293.90..286963.01 rows=941157 width=2029)
   (actual time=7.880..1062.384 rows=12 loops=1)
  Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
  Rows Removed by Filter: 1257498

Postgres does not expect filtering to be nearly as selective as it turns out to be. Column statistics don't seem to be up to date, either. ANALYZE might help or even VACUUM ANALYZE. Maybe more after increasing the STATISTICS target for selected columns, or some other tweaks. (Your server configuration and autovacuum settings should be appropriate in any case!)

Anyway, we don't even know how index_users_on_pending_user is defined - or any other details about your setup ...

Generally speaking, it's hard to estimate the number of qualifying rows after the many steps your two subqueries take. You would need accurate statistics to improve chances. A separate query with the definitive list of IDs is much easier to plan ...

Either way, you seem to be doing a lot of redundant work, even in your faster separate queries. Try this equivalent query:

SELECT u.*
FROM   company_users cu
JOIN   users u ON u.id = cu.user_id
WHERE  cu.deleted_at IS NULL
AND    cu.company_id = 1595
AND    EXISTS (
   SELECT
   FROM   bags b
   WHERE  b.deleted = false
   AND   (b.user_id = cu.user_id AND b.user_type = 'User'
      OR  b.id IN (SELECT bc.bag_id FROM bag_contributors bc WHERE bc.user_id = cu.user_id))
   AND    EXISTS (
      SELECT
      FROM   bag_items bi
      JOIN   item      i  ON i.id = bi.item_id
      WHERE  bi.bag_id  = b.id
      AND    bi.deleted = false                          
      AND    i.slug IN ('gray-hat', 'usb-cable')
      )
   );

Going out on a limb here, based on assumptions about the unknown setup.

Answer 4

You can just simplify and rewrite your query and get a big performance boost:

SELECT users.*
FROM users
INNER JOIN company_users ON users.id = company_users.user_id
    AND company_users.deleted IS NULL
    AND company_users.company_id = 1595
LEFT JOIN bag_contributors ON bag_contributors.user_id = users.id
INNER JOIN bags ON bags.deleted = FALSE
    AND (bags.id = bag_contributors.bag_id 
        OR (bags.user_type = 'User' AND bags.user_id = users.id))
INNER JOIN bag_items ON bag_items.deleted = FALSE
    AND bag_items.bag_id = bags.id
INNER JOIN items ON bag_items.item_id = item.id
    AND items.slug IN ('gray-hat', 'usb-cable');

This query should yield the same results, without multiple queries or any subqueries.