Oracle to Postgres — Index Skip Scan

I have been working on Oracle to Postgres migration and it has been a fun ride.

One of the recent encounters during migration was performance issue in Postgres for a query, actually quite a simple query.

SELECT count(*) AS num_incidents FROM audit_cs acs INNER JOIN audit_changes ac ON acs.audit_change_id = ac.audit_change_set WHERE acs.object_id=$1 AND ac.path = 'Flag' AND ac.new_value = 'sign' 

Let’s look at the execution plan in Postgres

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                      QUERY PLAN                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=416855.54..416855.55 rows=1 width=8) (actual time=1885.330..1885.331 rows=1 loops=1)                                                                                                                 |
|   Output: count(*)                                                                                                                                                                                                    |
|   Buffers: shared hit=192660 read=3                                                                                                                                                                                   |
|   ->  Nested Loop  (cost=1.13..416855.54 rows=1 width=0) (actual time=1885.324..1885.324 rows=0 loops=1)                                                                                                              |
|         Buffers: shared hit=192660 read=3                                                                                                                                                                             |
|         ->  Index Scan using i_ot_oi_cacs_comp on user.audit_cs acs  (cost=0.56..416382.89 rows=38 width=8) (actual time=1870.631..1882.638 rows=3 loops=1)                                                           |
|               Output: acs.audit_change_id, acs.date_record_added, acs.object_type, acs.object_id, acs.note, acs.created_by_user_id                                                                                    |
|               Index Cond: (acs.object_id = '20896385'::numeric)                                                                                                                                                       |
|               Buffers: shared hit=192647 read=1                                                                                                                                                                       |
|         ->  Index Scan using i_acs_audit_changes on user.audit_changes ac  (cost=0.56..12.42 rows=2 width=8) (actual time=0.890..0.890 rows=0 loops=3)                                                                |
|               Output: ac.audit_change_id, ac.path, ac.old_value, ac.new_value, ac.audit_change_set, ac.created_by                                                                                                     |
|               Index Cond: (ac.audit_change_set = acs.audit_change_id)                                                                                                                                                 |
|               Filter: (((ac.path)::text = 'Flag'::text) AND ((ac.new_value)::text = 'sign'::text))                                                                                                                    |
|               Rows Removed by Filter: 1                                                                                                                                                                               |
|               Buffers: shared hit=13 read=2                                                                                                                                                                           |
| Planning time: 0.932 ms                                                                                                                                                                                               |
| Execution time: 1885.401 ms                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(17 rows)

On a quick glance of the execution plan, it looks good as its reading the data using index, but to notice is the execution time of ~1.9secs and Buffers: shared hit of ~192k for index i_ot_oi_cacs_comp.

For the same sql, in Oracle, the optimizer uses Index Skip Scan on i_ot_oi_cacs_comp and completes within 2ms.

Does this give some hint 😉

Can the performance in Postgres be improved?

Firstly, lets review the definition of composite Index

"i_ot_oi_cacs_comp" btree (object_type, object_id)

Secondly, pg_stats gives a good understanding

select tablename, attname, inherited, null_frac, avg_width, n_distinct from pg_stats where tablename='audit_cs'

+------------------------+---------------------+-----------+-----------+-----------+------------+
|       tablename        |       attname       | inherited | null_frac | avg_width | n_distinct |
+------------------------+---------------------+-----------+-----------+-----------+------------+

|       audit_cs         | object_type         | f         |         0 |        14 |          1 |
|       audit_cs         | object_id           | f         |         0 |         6 |     750343 |

In the composte index, column object_type comes first and then object_id and Oracle handled it well with Index Skip Scan.

But do we really need object_type to be first column. The column object_id has much more distinct values when compared to object_type column. What happens if we change the column position for the index to

"i_oiot_cacs_comp" btree (object_id, object_type)

Lets the execution plan now

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                      QUERY PLAN                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=506.39..506.40 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1)                                                                                                                             |
|   Output: count(*)                                                                                                                                                                                                    |
|   Buffers: shared hit=21                                                                                                                                                                                              |
|   ->  Nested Loop  (cost=1.13..506.39 rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)                                                                                                                       |
|         Buffers: shared hit=21                                                                                                                                                                                        |
|         ->  Index Scan using i_oiot_cacs_comp on user.audit_cs acs  (cost=0.56..24.18 rows=39 width=8) (actual time=0.014..0.017 rows=3 loops=1)                                                                      |
|               Output: acs.audit_change_id, acs.date_record_added, acs.object_type, acs.object_id, acs.note, acs.created_by_user_id                                                                                    |
|               Index Cond: (acs.object_id = '20896385'::numeric)                                                                                                                                                       |
|               Buffers: shared hit=6                                                                                                                                                                                   |
|         ->  Index Scan using i_acs_audit_changes on user.audit_changes ac  (cost=0.56..12.34 rows=2 width=8) (actual time=0.007..0.007 rows=0 loops=3)                                                                |
|               Output: ac.audit_change_id, ac.path, ac.old_value, ac.new_value, ac.audit_change_set, ac.created_by                                                                                                     |
|               Index Cond: (ac.audit_change_set = acs.audit_change_id)                                                                                                                                                 |
|               Filter: (((ac.path)::text = 'Flag'::text) AND ((ac.new_value)::text = 'sign'::text))                                                                                                              |
|               Rows Removed by Filter: 1                                                                                                                                                                               |
|               Buffers: shared hit=15                                                                                                                                                                                  |
| Planning time: 0.441 ms                                                                                                                                                                                               |
| Execution time: 0.092 ms                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(17 rows)

Just with the simple change in column position, has reduced Buffer: Shared hits from 192K to 21 and execution time from ~2secs to less than 1ms.

+---------+------------------------+------------+------------------+---------------------------------------+-------------+-------+---------+----------------+
| snap_id |      sample_time       |  queryid   |    get_sqlid     |              short_query              | total_time  | calls |  mean   | percentage_cpu |
+---------+------------------------+------------+------------------+---------------------------------------+-------------+-------+---------+----------------+
|      41 | 2018-03-30 09:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 39237541.19 | 16794 | 2336.40 |           7.59 |
|      42 | 2018-03-30 10:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 35176645.84 | 16448 | 2138.66 |           6.80 |
|      43 | 2018-03-30 11:17:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 29582400.11 | 15951 | 1854.58 |           5.72 |
|      44 | 2018-03-30 12:17:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 34728899.68 | 16244 | 2137.95 |           6.72 |
|      46 | 2018-03-30 14:17:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 37036501.98 | 16442 | 2252.55 |           7.16 |
|      47 | 2018-03-30 15:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 35525730.37 | 15463 | 2297.47 |           6.87 |
|      49 | 2018-03-30 17:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 28899674.51 | 12425 | 2325.93 |           5.59 |
|      50 | 2018-03-30 18:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 35606631.89 | 14848 | 2398.08 |           6.89 |
|      51 | 2018-03-30 19:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 36339708.61 | 16049 | 2264.30 |           7.03 |
|      52 | 2018-03-30 20:17:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM | 29955685.60 | 13621 | 2199.23 |           5.79 |
|      73 | 2018-03-31 18:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    22705.92 | 31999 |    0.71 |           0.00 |
|      77 | 2018-03-31 23:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    13896.36 | 17489 |    0.79 |           0.00 |
|      78 | 2018-04-01 00:17:00+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    21180.69 | 37533 |    0.56 |           0.00 |
|      89 | 2018-04-03 04:00:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    37948.14 | 30610 |    1.24 |           0.01 |
|     106 | 2018-04-03 21:00:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    17327.80 | 21625 |    0.80 |           0.00 |
|     107 | 2018-04-03 22:00:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |    10792.78 | 13785 |    0.78 |           0.00 |
|     163 | 2018-04-06 06:00:01+00 | 2563607162 | 11a53a3f4909b816 | SELECT count(*) AS num_incidents FROM |     9255.47 | 16829 |    0.55 |           0.00 |
+---------+------------------------+------------+------------------+---------------------------------------+-------------+-------+---------+----------------+

So, when migrating from Oracle to Postgres, make sure to keep an eye on SQLs which perform Index Skip Scan in Oracle.

Advertisements

2 thoughts on “Oracle to Postgres — Index Skip Scan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s