Created
May 24, 2017 18:41
-
-
Save Millnert/53f471bdd7b173d09e15a60882082a78 to your computer and use it in GitHub Desktop.
Ceilometer (newton) GET /v2/resource/<resource_id> PostgreSQL query optimization
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- A ceilometer (newton) resource query can look like the following: | |
| --SQL: 'SELECT sample.meter_id AS sample_meter_id, meter.name AS meter_name, meter.type AS meter_type, meter.unit AS meter_unit, resource.resource_id AS resource_resource_id, resource.project_id AS resource_project_id, resource.source_id -- AS resource_source_id, resource.user_id AS resource_user_id | |
| -- FROM sample JOIN (SELECT max(sample.id) AS id | |
| -- FROM sample JOIN resource ON resource.internal_id = sample.resource_id | |
| -- WHERE resource.resource_id = %(resource_id_1)s GROUP BY sample.meter_id, resource.resource_id) AS anon_1 ON anon_1.id = sample.id JOIN meter ON meter.id = sample.meter_id JOIN resource ON resource.internal_id = sample.resource_id | |
| -- WHERE resource.resource_id = %(resource_id_2)s'] | |
| -- [parameters: {'resource_id_1': u'02c92f6c-6596-489e-a710-0321f3501256', 'resource_id_2': u'02c92f6c-6596-489e-a710-0321f3501256'} | |
| -- In the ceilometer database in question, the sample table has 141M rows and the resource table has 1.2M rows. | |
| -- Or, cleaned a bit: | |
| SELECT | |
| sample.meter_id AS sample_meter_id, | |
| meter.name AS meter_name, | |
| meter.type AS meter_type, | |
| meter.unit AS meter_unit, | |
| resource.resource_id AS resource_resource_id, | |
| resource.project_id AS resource_project_id, | |
| resource.source_id AS resource_source_id, | |
| resource.user_id AS resource_user_id | |
| FROM sample | |
| JOIN ( | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| GROUP BY | |
| sample.meter_id, resource.resource_id | |
| ) AS anon_1 ON | |
| anon_1.id = sample.id | |
| JOIN meter ON | |
| meter.id = sample.meter_id | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| -- The query returns the following: | |
| sample_meter_id | meter_name | meter_type | meter_unit | resource_resource_id | resource_project_id | resource_source_id | resource_user_id | |
| -----------------+---------------------+------------+------------+--------------------------------------+----------------------------------+--------------------+---------------------------------- | |
| 1 | vcpus | gauge | vcpu | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 2 | memory | gauge | MB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 3 | disk.ephemeral.size | gauge | GB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 4 | disk.root.size | gauge | GB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| (4 rows) | |
| Time: 31662.118 ms | |
| -- The EXPLAIN of this query is pretty terrible: | |
| ceilometer=# EXPLAIN SELECT | |
| ceilometer-# sample.meter_id AS sample_meter_id, | |
| ceilometer-# meter.name AS meter_name, | |
| ceilometer-# meter.type AS meter_type, | |
| ceilometer-# meter.unit AS meter_unit, | |
| ceilometer-# resource.resource_id AS resource_resource_id, | |
| ceilometer-# resource.project_id AS resource_project_id, | |
| ceilometer-# resource.source_id AS resource_source_id, | |
| ceilometer-# resource.user_id AS resource_user_id | |
| ceilometer-# FROM sample | |
| ceilometer-# JOIN ( | |
| ceilometer(# SELECT max(sample.id) AS id | |
| ceilometer(# FROM sample | |
| ceilometer(# JOIN resource ON | |
| ceilometer(# resource.internal_id = sample.resource_id | |
| ceilometer(# WHERE | |
| ceilometer(# resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| ceilometer(# GROUP BY | |
| ceilometer(# sample.meter_id, resource.resource_id | |
| ceilometer(# ) AS anon_1 ON | |
| ceilometer-# anon_1.id = sample.id | |
| ceilometer-# JOIN meter ON | |
| ceilometer-# meter.id = sample.meter_id | |
| ceilometer-# JOIN resource ON | |
| ceilometer-# resource.internal_id = sample.resource_id | |
| ceilometer-# WHERE | |
| ceilometer-# resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Nested Loop (cost=5538285.74..5556906.69 rows=1 width=1661) | |
| -> Hash Join (cost=5538285.60..5556901.32 rows=1 width=113) | |
| Hash Cond: (sample.resource_id = resource.internal_id) | |
| -> Nested Loop (cost=5538083.81..5556691.43 rows=2156 width=8) | |
| -> GroupAggregate (cost=5538083.24..5538159.05 rows=2156 width=45) | |
| Group Key: sample_1.meter_id, resource_1.resource_id | |
| -> Sort (cost=5538083.24..5538096.81 rows=5425 width=45) | |
| Sort Key: sample_1.meter_id | |
| -> Hash Join (cost=201.79..5537746.75 rows=5425 width=45) | |
| Hash Cond: (sample_1.resource_id = resource_1.internal_id) | |
| -> Seq Scan on sample sample_1 (cost=0.00..5046007.06 rows=131062306 width=12) | |
| -> Hash (cost=201.18..201.18 rows=49 width=41) | |
| -> Index Scan using ix_resource_resource_id on resource resource_1 (cost=0.43..201.18 rows=49 width=41) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| -> Index Scan using meter_pkey on sample (cost=0.57..8.58 rows=1 width=12) | |
| Index Cond: (id = (max(sample_1.id))) | |
| -> Hash (cost=201.18..201.18 rows=49 width=113) | |
| -> Index Scan using ix_resource_resource_id on resource (cost=0.43..201.18 rows=49 width=113) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| -> Index Scan using meter_pkey1 on meter (cost=0.14..5.36 rows=1 width=1552) | |
| Index Cond: (id = sample.meter_id) | |
| (21 rows) | |
| Time: 1.428 ms | |
| -- For those unfamiliar with SQL statament planning engines, the above is esentially saying that the engine will resolve to perform using a very expensive inner Sequence Scan. This happens despite there being ample indexes around: | |
| ceilometer=# \d sample | |
| Table "public.sample" | |
| Column | Type | Modifiers | |
| -------------------+-----------------------------+---------------------------------------------------- | |
| id | integer | not null default nextval('meter_id_seq'::regclass) | |
| volume | double precision | | |
| timestamp | timestamp without time zone | | |
| message_signature | character varying(64) | | |
| message_id | character varying(128) | | |
| recorded_at | timestamp without time zone | | |
| meter_id | integer | | |
| resource_id | integer | | |
| Indexes: | |
| "meter_pkey" PRIMARY KEY, btree (id) | |
| "ix_sample_meter_id" btree (meter_id) | |
| "ix_sample_meter_id_resource_id" btree (meter_id, resource_id) | |
| "ix_sample_resource_id" btree (resource_id) | |
| "ix_sample_timestamp" btree ("timestamp") | |
| Foreign-key constraints: | |
| "sample_meter_id_fkey" FOREIGN KEY (meter_id) REFERENCES meter(id) | |
| "sample_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES resource(internal_id) | |
| -- Importantly, expensive Sequence Scan can be averted. We continue investigating the subquery from above: | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| GROUP BY | |
| sample.meter_id, resource.resource_id; | |
| -- This subquery executes as follows: | |
| ceilometer=# SELECT max(sample.id) AS id | |
| ceilometer-# FROM sample | |
| ceilometer-# JOIN resource ON | |
| ceilometer-# resource.internal_id = sample.resource_id | |
| ceilometer-# WHERE | |
| ceilometer-# resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| ceilometer-# GROUP BY | |
| ceilometer-# sample.meter_id, resource.resource_id; | |
| id | |
| ----------- | |
| 137964934 | |
| 137964935 | |
| 137964936 | |
| 137964937 | |
| (4 rows) | |
| Time: 31156.401 ms | |
| -- I.e. essentially all the execution time is in this subquery. | |
| -- The output corresponds to the the 4 most recent samples of the 4 meters in the database for the resource in question. | |
| -- Studying the query, we see that we're doing a JOIN between the tables "resource" and "sample" based on resource.resource_id (text) being equal to a given parameter. It returns the value from the aggregate function max(), which is grouped by the meter_id. The second GROUP BY clause seems redundant, and possibly comes from SQLA. | |
| ceilometer=# SELECT max(sample.id) AS id | |
| ceilometer-# FROM sample | |
| ceilometer-# JOIN resource | |
| ceilometer-# ON resource.internal_id = sample.resource_id | |
| ceilometer-# WHERE resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| ceilometer-# GROUP BY sample.meter_id, resource.resource_id; | |
| id | |
| ----------- | |
| 137964934 | |
| 137964935 | |
| 137964936 | |
| 137964937 | |
| (4 rows) | |
| Time: 31224.268 ms | |
| -- Analyzing this particular query yields the following result: | |
| ceilometer=# EXPLAIN SELECT max(sample.id) AS id | |
| ceilometer-# FROM sample | |
| ceilometer-# JOIN resource ON | |
| ceilometer-# resource.internal_id = sample.resource_id | |
| ceilometer-# WHERE | |
| ceilometer-# resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| ceilometer-# GROUP BY | |
| ceilometer-# sample.meter_id, resource.resource_id; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------- | |
| GroupAggregate (cost=5538490.92..5538566.73 rows=2156 width=45) | |
| Group Key: sample.meter_id, resource.resource_id | |
| -> Sort (cost=5538490.92..5538504.48 rows=5425 width=45) | |
| Sort Key: sample.meter_id | |
| -> Hash Join (cost=201.79..5538154.42 rows=5425 width=45) | |
| Hash Cond: (sample.resource_id = resource.internal_id) | |
| -> Seq Scan on sample (cost=0.00..5046378.55 rows=131071955 width=12) | |
| -> Hash (cost=201.18..201.18 rows=49 width=41) | |
| -> Index Scan using ix_resource_resource_id on resource (cost=0.43..201.18 rows=49 width=41) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| (10 rows) | |
| -- The query is very straighforward: pick out all resource's with a specific resource_id, and then use the reference from these to resolve a number of samples. And we can see that the cost is very high, and this is identified by the Sequence Scan on the sample table. | |
| -- The reason the engine resolves to a Sequence Scan here is according to resources on the internet that the it cannot know how many rows will return from the Index Scan done to resolve the resource row. | |
| -- We now compare this with the base case, where it is known a prioriy for the planner how many rows are coming from the most inner scan, by changing the query. | |
| -- But first we check how many matches there are in the resource table for the resource in question: | |
| ceilometer=# SELECT internal_id FROM resource WHERE resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| internal_id | |
| ------------- | |
| 1170738 | |
| 1170428 | |
| 1170427 | |
| 1170426 | |
| 1170425 | |
| 1170424 | |
| 1170423 | |
| 1170422 | |
| 1170421 | |
| 1170420 | |
| 1170419 | |
| 1170418 | |
| 1170417 | |
| 1170416 | |
| 1170415 | |
| 1170414 | |
| 1170413 | |
| (17 rows) | |
| -- If we then supply these numbers to the query up front, the planner can do a much better job: | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.internal_id IN (1170738, 1170428, 1170427, 1170426, 1170425, 1170424, 1170423, 1170422, 1170421, 1170420, 1170419, 1170418, 1170417, 1170416, 1170415, 1170414, 1170413) | |
| GROUP BY | |
| sample.meter_id, resource.resource_id; | |
| ceilometer=# EXPLAIN SELECT max(sample.id) AS id | |
| ceilometer-# FROM sample | |
| ceilometer-# JOIN resource ON | |
| ceilometer-# resource.internal_id = sample.resource_id | |
| ceilometer-# WHERE | |
| ceilometer-# resource.internal_id IN (1170738, 1170428, 1170427, 1170426, 1170425, 1170424, 1170423, 1170422, 1170421, 1170420, 1170419, 1170418, 1170417, 1170416, 1170415, 1170414, 1170413) | |
| ceilometer-# GROUP BY | |
| ceilometer-# sample.meter_id, resource.resource_id; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| GroupAggregate (cost=2087646.07..2087672.37 rows=748 width=45) | |
| Group Key: sample.meter_id, resource.resource_id | |
| -> Sort (cost=2087646.07..2087650.77 rows=1882 width=45) | |
| Sort Key: sample.meter_id, resource.resource_id | |
| -> Nested Loop (cost=0.99..2087543.70 rows=1882 width=45) | |
| -> Index Scan using resource_pkey on resource (cost=0.43..83.57 rows=17 width=41) | |
| Index Cond: (internal_id = ANY ('{1170738,1170428,1170427,1170426,1170425,1170424,1170423,1170422,1170421,1170420,1170419,1170418,1170417,1170416,1170415,1170414,1170413}'::integer[])) | |
| -> Index Scan using ix_sample_resource_id on sample (cost=0.57..122391.65 rows=40012 width=12) | |
| Index Cond: (resource_id = resource.internal_id) | |
| (9 rows) | |
| -- The most important result of this simplified query is that there is now an Index Scan happening on the resource table, instead of a Sequence Scan on the sample table. | |
| -- If these numbers are known already, there's little reason to do the JOIN however. Dropping the JOIN with resource also means we can't do the redundant GROUP BY clause. The following query is functionally equivalent: | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id IN (1170738, 1170428, 1170427, 1170426, 1170425, 1170424, 1170423, 1170422, 1170421, 1170420, 1170419, 1170418, 1170417, 1170416, 1170415, 1170414, 1170413) | |
| GROUP BY | |
| sample.meter_id; | |
| -- And when we do an EXPLAIN we can see that the query has been reduced in cost by 40% more, and the previous Index Scan on the resource table improvement, has now been further improved to a Bitmap Heap Scan by the planner, on the sample table. | |
| ceilometer=# EXPLAIN SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id IN (1170738, 1170428, 1170427, 1170426, 1170425, 1170424, 1170423, 1170422, 1170421, 1170420, 1170419, 1170418, 1170417, 1170416, 1170415, 1170414, 1170413) | |
| GROUP BY | |
| sample.meter_id; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| HashAggregate (cost=1391173.70..1391174.14 rows=44 width=8) | |
| Group Key: meter_id | |
| -> Bitmap Heap Scan on sample (cost=11163.72..1388707.85 rows=493171 width=8) | |
| Recheck Cond: (resource_id = ANY ('{1170738,1170428,1170427,1170426,1170425,1170424,1170423,1170422,1170421,1170420,1170419,1170418,1170417,1170416,1170415,1170414,1170413}'::integer[])) | |
| -> Bitmap Index Scan on ix_sample_resource_id (cost=0.00..11040.42 rows=493171 width=0) | |
| Index Cond: (resource_id = ANY ('{1170738,1170428,1170427,1170426,1170425,1170424,1170423,1170422,1170421,1170420,1170419,1170418,1170417,1170416,1170415,1170414,1170413}'::integer[])) | |
| (6 rows) | |
| -- The cost improvement translates to a marginal execution speed gain also, from something like average of ~0.7 ms to ~0.5 ms. | |
| -- The goal should be clear by now: We want to advise the planner to dare to use the indexes. The subquery can be rewritten as follows: | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id = ANY ( | |
| (SELECT array_agg(internal_id) | |
| FROM resource | |
| WHERE | |
| resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| )::bigint[] | |
| ) | |
| GROUP BY sample.meter_id; | |
| -- Analyzing this query yields a comparatively very performant subquery: | |
| ceilometer=# EXPLAIN SELECT max(sample.id) AS id | |
| ceilometer-# FROM sample | |
| ceilometer-# WHERE | |
| ceilometer-# sample.resource_id = ANY ( | |
| ceilometer(# (SELECT array_agg(internal_id) | |
| ceilometer(# FROM resource | |
| ceilometer(# WHERE | |
| ceilometer(# resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| ceilometer(# )::bigint[] | |
| ceilometer(# ) | |
| ceilometer-# GROUP BY sample.meter_id; | |
| QUERY PLAN | |
| --------------------------------------------------------------------------------------------------------- | |
| HashAggregate (cost=1185003.03..1185003.47 rows=44 width=8) | |
| Group Key: sample.meter_id | |
| InitPlan 1 (returns $0) | |
| -> Aggregate (cost=201.30..201.31 rows=1 width=32) | |
| -> Index Scan using ix_resource_resource_id on resource (cost=0.43..201.18 rows=49 width=4) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| -> Bitmap Heap Scan on sample (cost=9054.83..1182803.56 rows=399631 width=8) | |
| Recheck Cond: (resource_id = ANY (($0)::bigint[])) | |
| -> Bitmap Index Scan on ix_sample_resource_id (cost=0.00..8954.92 rows=399631 width=0) | |
| Index Cond: (resource_id = ANY (($0)::bigint[])) | |
| (10 rows) | |
| -- The performance of the new subquery is on par with the times above -- we can't really achieve the best case above (supplying the internal_ids directly in the QUERY), but that's OK as that's unrealistic. | |
| ceilometer=# SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id = ANY ( | |
| (SELECT array_agg(internal_id) | |
| FROM resource | |
| WHERE | |
| resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| )::bigint[] | |
| ) | |
| GROUP BY sample.meter_id; | |
| id | |
| ----------- | |
| 137964934 | |
| 137964936 | |
| 137964937 | |
| 137964935 | |
| (4 rows) | |
| Time: 0.634 ms | |
| -- 0.634 ms vs 31224.268 ms for the subquery is an increase in performance of ~50000X (49249X). That's pretty OK. | |
| -- Putting all of this together now, we can re-write the original query as follows: | |
| SELECT | |
| sample.meter_id AS sample_meter_id, | |
| meter.name AS meter_name, | |
| meter.type AS meter_type, | |
| meter.unit AS meter_unit, | |
| resource.resource_id AS resource_resource_id, | |
| resource.project_id AS resource_project_id, | |
| resource.source_id AS resource_source_id, | |
| resource.user_id AS resource_user_id | |
| FROM sample | |
| JOIN ( | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id = ANY ( | |
| (SELECT array_agg(internal_id) | |
| FROM resource | |
| WHERE | |
| resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| )::bigint[] | |
| ) | |
| GROUP BY sample.meter_id | |
| ) AS anon_1 ON | |
| anon_1.id = sample.id | |
| JOIN meter ON | |
| meter.id = sample.meter_id | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| -- Analyzing the new query we see a much better cost picture: | |
| ceilometer=# EXPLAIN SELECT | |
| sample.meter_id AS sample_meter_id, | |
| meter.name AS meter_name, | |
| meter.type AS meter_type, | |
| meter.unit AS meter_unit, | |
| resource.resource_id AS resource_resource_id, | |
| resource.project_id AS resource_project_id, | |
| resource.source_id AS resource_source_id, | |
| resource.user_id AS resource_user_id | |
| FROM sample | |
| JOIN ( | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id = ANY ( | |
| (SELECT array_agg(internal_id) | |
| FROM resource | |
| WHERE | |
| resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| )::bigint[] | |
| ) | |
| GROUP BY sample.meter_id | |
| ) AS anon_1 ON | |
| anon_1.id = sample.id | |
| JOIN meter ON | |
| meter.id = sample.meter_id | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------------------------------------------------- | |
| Nested Loop (cost=1185401.47..1185607.64 rows=1 width=1661) | |
| -> Hash Join (cost=1185401.33..1185602.27 rows=1 width=113) | |
| Hash Cond: (resource.internal_id = sample.resource_id) | |
| -> Index Scan using ix_resource_resource_id on resource (cost=0.43..201.18 rows=49 width=113) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| -> Hash (cost=1185400.35..1185400.35 rows=44 width=8) | |
| -> Nested Loop (cost=1185021.86..1185400.35 rows=44 width=8) | |
| -> HashAggregate (cost=1185021.29..1185021.73 rows=44 width=8) | |
| Group Key: sample_1.meter_id | |
| InitPlan 1 (returns $0) | |
| -> Aggregate (cost=201.30..201.31 rows=1 width=32) | |
| -> Index Scan using ix_resource_resource_id on resource resource_1 (cost=0.43..201.18 rows=49 width=4) | |
| Index Cond: ((resource_id)::text = '02c92f6c-6596-489e-a710-0321f3501256'::text) | |
| -> Bitmap Heap Scan on sample sample_1 (cost=9054.91..1182821.80 rows=399637 width=8) | |
| Recheck Cond: (resource_id = ANY (($0)::bigint[])) | |
| -> Bitmap Index Scan on ix_sample_resource_id (cost=0.00..8955.00 rows=399637 width=0) | |
| Index Cond: (resource_id = ANY (($0)::bigint[])) | |
| -> Index Scan using meter_pkey on sample (cost=0.57..8.59 rows=1 width=12) | |
| Index Cond: (id = (max(sample_1.id))) | |
| -> Index Scan using meter_pkey1 on meter (cost=0.14..5.36 rows=1 width=1552) | |
| Index Cond: (id = sample.meter_id) | |
| (21 rows) | |
| -- As we can see, we're doing a few more relatively expensive joins but they're all using efficient Index Scans. | |
| -- This new query then performs as follows: | |
| ceilometer=# SELECT | |
| sample.meter_id AS sample_meter_id, | |
| meter.name AS meter_name, | |
| meter.type AS meter_type, | |
| meter.unit AS meter_unit, | |
| resource.resource_id AS resource_resource_id, | |
| resource.project_id AS resource_project_id, | |
| resource.source_id AS resource_source_id, | |
| resource.user_id AS resource_user_id | |
| FROM sample | |
| JOIN ( | |
| SELECT max(sample.id) AS id | |
| FROM sample | |
| WHERE | |
| sample.resource_id = ANY ( | |
| (SELECT array_agg(internal_id) | |
| FROM resource | |
| WHERE | |
| resource_id = '02c92f6c-6596-489e-a710-0321f3501256' | |
| )::bigint[] | |
| ) | |
| GROUP BY sample.meter_id | |
| ) AS anon_1 ON | |
| anon_1.id = sample.id | |
| JOIN meter ON | |
| meter.id = sample.meter_id | |
| JOIN resource ON | |
| resource.internal_id = sample.resource_id | |
| WHERE | |
| resource.resource_id = '02c92f6c-6596-489e-a710-0321f3501256'; | |
| sample_meter_id | meter_name | meter_type | meter_unit | resource_resource_id | resource_project_id | resource_source_id | resource_user_id | |
| -----------------+---------------------+------------+------------+--------------------------------------+----------------------------------+--------------------+---------------------------------- | |
| 2 | memory | gauge | MB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 4 | disk.root.size | gauge | GB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 3 | disk.ephemeral.size | gauge | GB | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| 1 | vcpus | gauge | vcpu | 02c92f6c-6596-489e-a710-0321f3501256 | 5ccf1bc2dfc141c7b6a73df5aaa34d8e | openstack | f9040d5053464ac7bf5b1ec704be434e | |
| (4 rows) | |
| Time: 1.023 ms | |
| -- 1.023 ms is pretty snappy, compared with the original 31662.118 ms, an increase by 30000 X (30950 X). And importantly, it's not scaling with the number of samples anymore, which the original Sequence Scan does. It's.. way much more speedy, and should stay so even in the face of major growth of the samples table. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment