0

Morning everyone We have migrated from postgres 10.6 to postgres 11.7 recently and we're facing some performance issues with some of our queries Specifically with this particular query:

SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
        OR credits.contentRef = 'local-test-10:contents-show-156789'
        OR credits.contentRef = 'local-test-10:contents-episode-227267'
        OR credits.contentRef = 'local-test-10:contents-episode-388789'
        OR credits.contentRef = 'local-test-10:contents-episode-870245'
        OR credits.contentRef = 'local-test-10:contents-episode-280712')
       AND credits.owner = 'local-test-10')
ORDER BY credits.id ASC
LIMIT 100

The table credits has several indexes, one of them for the column contentref:

"credits_contentref_idx" btree (contentref NULLS FIRST, id)

In postgres 10, the planner combines that index and the performance is quite good:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                              |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=3500.92..3501.17 rows=100 width=50) (actual time=0.049..0.049 rows=0 loops=1)                                                                                                                                                                                                                                                                                                                              |
|   ->  Sort  (cost=3500.92..3501.99 rows=429 width=50) (actual time=0.048..0.048 rows=0 loops=1)                                                                                                                                                                                                                                                                                                                         |
|         Sort Key: id                                                                                                                                                                                                                                                                                                                                                                                                    |
|         Sort Method: quicksort  Memory: 25kB                                                                                                                                                                                                                                                                                                                                                                            |
|         ->  Bitmap Heap Scan on credits  (cost=58.60..3484.52 rows=429 width=50) (actual time=0.043..0.043 rows=0 loops=1)                                                                                                                                                                                                                                                                                              |
|               Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
|               Filter: (owner = 'local-test-10'::text)                                                                                                                                                                                                                                                                                                                                                                   |
|               ->  BitmapOr  (cost=58.60..58.60 rows=881 width=0) (actual time=0.042..0.042 rows=0 loops=1)                                                                                                                                                                                                                                                                                                              |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.016..0.016 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text)                                                                                                                                                                                                                                                                                                                      |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.006..0.006 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-show-156789'::text)                                                                                                                                                                                                                                                                                                                         |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.006..0.006 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-227267'::text)                                                                                                                                                                                                                                                                                                                      |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.004..0.004 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-388789'::text)                                                                                                                                                                                                                                                                                                                      |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.005..0.005 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-870245'::text)                                                                                                                                                                                                                                                                                                                      |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..9.66 rows=147 width=0) (actual time=0.004..0.004 rows=0 loops=1)                                                                                                                                                                                                                                                                       |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-280712'::text)                                                                                                                                                                                                                                                                                                                      |
| Planning time: 0.246 ms                                                                                                                                                                                                                                                                                                                                                                                                 |
| Execution time: 0.086 ms                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

But using postgres 11.7, the planner doesn't use that index and the performance is quite bad

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                  |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=0.56..7522.67 rows=100 width=50) (actual time=4159.270..4159.270 rows=0 loops=1)                                                                                                                                                                                                                                                                                                               |
|   ->  Index Scan using credits_owner_idx on credits  (cost=0.56..910176.43 rows=12100 width=50) (actual time=4159.268..4159.268 rows=0 loops=1)                                                                                                                                                                                                                                                             |
|         Index Cond: (owner = 'local-test-10'::text)                                                                                                                                                                                                                                                                                                                                                         |
|         Filter: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
|         Rows Removed by Filter: 3318877                                                                                                                                                                                                                                                                                                                                                                     |
| Planning Time: 0.278 ms                                                                                                                                                                                                                                                                                                                                                                                     |
| Execution Time: 4159.746 ms                                                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

If I remove the order by in Postgres 11, the planner combine the index and the performance is good again

SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
        OR credits.contentRef = 'local-test-10:contents-show-156789'
        OR credits.contentRef = 'local-test-10:contents-episode-227267'
        OR credits.contentRef = 'local-test-10:contents-episode-388789'
        OR credits.contentRef = 'local-test-10:contents-episode-870245'
        OR credits.contentRef = 'local-test-10:contents-episode-280712')
       AND credits.owner = 'local-test-10')
LIMIT 100
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                        |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=766.23..1109.72 rows=100 width=34) (actual time=0.049..0.049 rows=0 loops=1)                                                                                                                                                                                                                                                                                                                         |
|   ->  Bitmap Heap Scan on credits  (cost=766.23..42328.05 rows=12100 width=34) (actual time=0.048..0.048 rows=0 loops=1)                                                                                                                                                                                                                                                                                          |
|         Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
|         Filter: (owner = 'local-test-10'::text)                                                                                                                                                                                                                                                                                                                                                                   |
|         ->  BitmapOr  (cost=766.23..766.23 rows=12902 width=0) (actual time=0.044..0.044 rows=0 loops=1)                                                                                                                                                                                                                                                                                                          |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.017..0.017 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text)                                                                                                                                                                                                                                                                                                                      |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.006..0.006 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-show-156789'::text)                                                                                                                                                                                                                                                                                                                         |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.006..0.006 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-episode-227267'::text)                                                                                                                                                                                                                                                                                                                      |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.005..0.005 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-episode-388789'::text)                                                                                                                                                                                                                                                                                                                      |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.005..0.005 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-episode-870245'::text)                                                                                                                                                                                                                                                                                                                      |
|               ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..124.68 rows=2150 width=0) (actual time=0.004..0.004 rows=0 loops=1)                                                                                                                                                                                                                                                                    |
|                     Index Cond: (contentref = 'local-test-10:contents-episode-280712'::text)                                                                                                                                                                                                                                                                                                                      |
| Planning Time: 0.235 ms                                                                                                                                                                                                                                                                                                                                                                                           |
| Execution Time: 0.139 ms                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Still not as good as Postgres 10 So far, I've tried:

  • Instead using OR using IN or ARRAY and ANY --> doesn't improve
  • Create an index over the 3 columns used in the query (contentref, owner, id) --> doesn't improve

I'm out of ideas to improve the performance of the query, and I don't know why the planner is not combining the index, as it did in earlier version

Any ideas?

Update If I reduce the number of "OR" in the query, the planner combine the indexes:

SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
        OR credits.contentRef = 'local-test-10:contents-show-156789')
       AND credits.owner = 'local-test-10')
ORDER BY credits.id ASC
LIMIT 100
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                              |
|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=16796.49..16796.74 rows=100 width=50) (actual time=0.027..0.027 rows=0 loops=1)                                                            |
|   ->  Sort  (cost=16796.49..16807.26 rows=4307 width=50) (actual time=0.027..0.027 rows=0 loops=1)                                                      |
|         Sort Key: id                                                                                                                                    |
|         Sort Method: quicksort  Memory: 25kB                                                                                                            |
|         ->  Bitmap Heap Scan on credits  (cost=269.82..16631.88 rows=4307 width=50) (actual time=0.021..0.021 rows=0 loops=1)                           |
|               Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text)) |
|               Filter: (owner = 'local-test-10'::text)                                                                                                   |
|               ->  BitmapOr  (cost=269.82..269.82 rows=4608 width=0) (actual time=0.017..0.017 rows=0 loops=1)                                           |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..133.84 rows=2304 width=0) (actual time=0.010..0.010 rows=0 loops=1)    |
|                           Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text)                                                      |
|                     ->  Bitmap Index Scan on credits_contentref_idx  (cost=0.00..133.84 rows=2304 width=0) (actual time=0.006..0.006 rows=0 loops=1)    |
|                           Index Cond: (contentref = 'local-test-10:contents-show-156789'::text)                                                         |
| Planning Time: 0.256 ms                                                                                                                                 |
| Execution Time: 0.079 ms                                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+

I've checked that including 2 ORs the planner combines the indexes, but if I adde 3 or more ORs, then the planner doesn't combine it

  • Did you run vacuum analyze on the tables in question after the upgrde? – a_horse_with_no_name Aug 25 at 5:38
  • Yes, I run it and no difference – JavierGarciaPiksel Aug 25 at 6:06
  • Is there any index on credits.id? – McNets Aug 25 at 7:12
  • yes, we have created an index on that column ``` "credits_pkey" PRIMARY KEY, btree (id) ``` – JavierGarciaPiksel Aug 25 at 7:13
  • Can a credit with contentRef = 'local-test-10:contents-episode-421254' have an owner other than 'local-test-10" ? – Gerard H. Pille Aug 25 at 13:41
0

Ok, I finally solved the problem by playing with the statistics I just created 2 new statistics and modify the statistics for the column contentref:

ALTER TABLE credits ALTER contentref SET STATISTICS 10000
CREATE STATISTICS credits_contentref_owner (dependencies) ON contentref, owner FROM credits;
CREATE STATISTICS credits_contentref_ref (dependencies) ON contentref, ref FROM credits;

Basically I have configured postgres to:

  • use more different values of the column contentref to estimate the rows to check
  • exists a correlation between the column contentref and owner, so knowing the contentref postgres will know the value of owner
  • exists a correlation between the column contentref and ref, so knowing the contentref postgres will know the value of ref

By doing that, the plan for the query is more accurate so the query runs better

| improve this answer | |

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.