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.
2 thoughts on “Oracle to Postgres — Index Skip Scan”
Thanks for posting this Anand. May I ask you how big this database that you are migrating?
Thanks for visiting the blog, Leo. The data set we are migrating to Postgres is ~1.5Tb.