Last active
December 9, 2018 21:22
-
-
Save odony/71f8b3b2e2f634ff534032128e738785 to your computer and use it in GitHub Desktop.
10.0 stock.quant GROUP BY timings for translations
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
| 10e=# SELECT (SELECT COUNT(*) FROM product_product), (SELECT COUNT(*) FROM stock_quant), (SELECT COUNT(*) FROM ir_translation); | |
| count | count | count | |
| -------+-------+-------- | |
| 41072 | 42082 | 409600 | |
| (1 row) | |
| 10e=# show work_mem; | |
| work_mem | |
| ---------- | |
| 64MB | |
| (1 row) | |
| 10e=# EXPLAIN ANALYZE | |
| SELECT min("stock_quant".id) AS id, count("stock_quant".id) AS "product_id_count" , sum("stock_quant"."qty") AS "qty" ,"stock_quant"."product_id" as "product_id" | |
| FROM "stock_quant" LEFT JOIN "product_product" as "stock_quant__product_id" ON ("stock_quant"."product_id" = "stock_quant__product_id"."id") | |
| LEFT JOIN "product_template" as "stock_quant__product_id__product_tmpl_id" ON ("stock_quant__product_id"."product_tmpl_id" = "stock_quant__product_id__product_tmpl_id"."id") LEFT JOIN | |
| (SELECT DISTINCT ON (res_id) res_id, value | |
| FROM "ir_translation" | |
| WHERE name='product.template,name' AND lang='fr_FR' AND value!='' | |
| ORDER BY res_id, id DESC) | |
| as "stock_quant__product_id__product_tmpl_id__name" ON ("stock_quant__product_id__product_tmpl_id"."id" = "stock_quant__product_id__product_tmpl_id__name"."res_id") | |
| WHERE ("stock_quant"."location_id" in (15,19,18,38)) | |
| GROUP BY "stock_quant"."product_id","stock_quant__product_id"."default_code",COALESCE("stock_quant__product_id__product_tmpl_id__name"."value","stock_quant__product_id__product_tmpl_id"."name"),"stock_quant__product_id"."id" | |
| ORDER BY "stock_quant__product_id"."default_code" ,COALESCE("stock_quant__product_id__product_tmpl_id__name"."value", "stock_quant__product_id__product_tmpl_id"."name") ,"stock_quant__product_id"."id" | |
| LIMIT 80; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Limit (cost=59828.82..59829.02 rows=80 width=59) (actual time=1128.136..1128.142 rows=80 loops=1) | |
| -> Sort (cost=59828.82..59933.86 rows=42014 width=59) (actual time=1128.135..1128.137 rows=80 loops=1) | |
| Sort Key: stock_quant__product_id.default_code, (COALESCE(stock_quant__product_id__product_tmpl_id__name.value, (stock_quant__product_id__product_tmpl_id.name)::text)), stock_quant__product_id.id | |
| Sort Method: top-N heapsort Memory: 31kB | |
| -> HashAggregate (cost=57870.56..58290.70 rows=42014 width=59) (actual time=1090.470..1105.479 rows=41068 loops=1) | |
| -> Hash Right Join (cost=48257.48..57135.32 rows=42014 width=59) (actual time=1013.387..1071.331 rows=42081 loops=1) | |
| Hash Cond: (stock_quant__product_id__product_tmpl_id.id = stock_quant__product_id.product_tmpl_id) | |
| -> Hash Left Join (cost=44376.21..52012.54 rows=41069 width=35) (actual time=960.095..988.399 rows=41069 loops=1) | |
| Hash Cond: (stock_quant__product_id__product_tmpl_id.id = stock_quant__product_id__product_tmpl_id__name.res_id) | |
| -> Seq Scan on product_template stock_quant__product_id__product_tmpl_id (cost=0.00..7480.69 rows=41069 width=18) (actual time=0.030..17.082 rows=41069 loops=1) | |
| -> Hash (cost=44374.17..44374.17 rows=163 width=21) (actual time=960.052..960.052 rows=0 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 0kB | |
| -> Subquery Scan on stock_quant__product_id__product_tmpl_id__name (cost=42710.53..44374.17 rows=163 width=21) (actual time=927.723..960.050 rows=1 loops=1) | |
| -> Unique (cost=42710.53..44372.54 rows=163 width=25) (actual time=927.721..960.047 rows=1 loops=1) | |
| -> Sort (cost=42710.53..43541.54 rows=332402 width=25) (actual time=927.719..942.721 rows=360448 loops=1) | |
| Sort Key: ir_translation.res_id, ir_translation.id | |
| Sort Method: quicksort Memory: 40449kB | |
| -> Seq Scan on ir_translation (cost=0.00..12225.00 rows=332402 width=25) (actual time=0.008..126.400 rows=360448 loops=1) | |
| Filter: ((value <> ''::text) AND ((name)::text = 'product.template,name'::text) AND ((lang)::text = 'fr_FR'::text)) | |
| Rows Removed by Filter: 49152 | |
| -> Hash (cost=3356.09..3356.09 rows=42014 width=32) (actual time=53.279..53.279 rows=42081 loops=1) | |
| Buckets: 8192 Batches: 1 Memory Usage: 2303kB | |
| -> Hash Left Join (cost=1354.12..3356.09 rows=42014 width=32) (actual time=12.653..45.598 rows=42081 loops=1) | |
| Hash Cond: (stock_quant.product_id = stock_quant__product_id.id) | |
| -> Seq Scan on stock_quant (cost=0.00..1214.21 rows=42014 width=16) (actual time=0.007..8.004 rows=42081 loops=1) | |
| Filter: (location_id = ANY ('{15,19,18,38}'::integer[])) | |
| Rows Removed by Filter: 1 | |
| -> Hash (cost=840.72..840.72 rows=41072 width=16) (actual time=12.627..12.627 rows=41072 loops=1) | |
| Buckets: 8192 Batches: 1 Memory Usage: 1605kB | |
| -> Seq Scan on product_product stock_quant__product_id (cost=0.00..840.72 rows=41072 width=16) (actual time=0.004..6.204 rows=41072 loops=1) | |
| Total runtime: 1130.251 ms | |
| (31 rows) | |
| 10e=# CREATE INDEX ON ir_translation (name, lang, res_id, id desc) WHERE value != ''; | |
| CREATE INDEX | |
| 10e=# EXPLAIN ANALYZE | |
| SELECT min("stock_quant".id) AS id, count("stock_quant".id) AS "product_id_count" , sum("stock_quant"."qty") AS "qty" ,"stock_quant"."product_id" as "product_id" | |
| FROM "stock_quant" LEFT JOIN "product_product" as "stock_quant__product_id" ON ("stock_quant"."product_id" = "stock_quant__product_id"."id") | |
| LEFT JOIN "product_template" as "stock_quant__product_id__product_tmpl_id" ON ("stock_quant__product_id"."product_tmpl_id" = "stock_quant__product_id__product_tmpl_id"."id") LEFT JOIN | |
| (SELECT DISTINCT ON (res_id) res_id, value | |
| FROM "ir_translation" | |
| WHERE name='product.template,name' AND lang='fr_FR' AND value!='' | |
| ORDER BY res_id, id DESC) | |
| as "stock_quant__product_id__product_tmpl_id__name" ON ("stock_quant__product_id__product_tmpl_id"."id" = "stock_quant__product_id__product_tmpl_id__name"."res_id") | |
| WHERE ("stock_quant"."location_id" in (15,19,18,38)) | |
| GROUP BY "stock_quant"."product_id","stock_quant__product_id"."default_code",COALESCE("stock_quant__product_id__product_tmpl_id__name"."value","stock_quant__product_id__product_tmpl_id"."name"),"stock_quant__product_id"."id" | |
| ORDER BY "stock_quant__product_id"."default_code" ,COALESCE("stock_quant__product_id__product_tmpl_id__name"."value", "stock_quant__product_id__product_tmpl_id"."name") ,"stock_quant__product_id"."id" | |
| LIMIT 80; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |
| Limit (cost=48554.28..48554.48 rows=80 width=59) (actual time=280.724..280.732 rows=80 loops=1) | |
| -> Sort (cost=48554.28..48659.31 rows=42014 width=59) (actual time=280.722..280.726 rows=80 loops=1) | |
| Sort Key: stock_quant__product_id.default_code, (COALESCE(ir_translation.value, (stock_quant__product_id__product_tmpl_id.name)::text)), stock_quant__product_id.id | |
| Sort Method: top-N heapsort Memory: 31kB | |
| -> HashAggregate (cost=46596.02..47016.16 rows=42014 width=59) (actual time=247.814..259.779 rows=41068 loops=1) | |
| -> Hash Right Join (cost=11875.74..45860.78 rows=42014 width=59) (actual time=203.734..229.561 rows=42081 loops=1) | |
| Hash Cond: (stock_quant__product_id__product_tmpl_id.id = stock_quant__product_id.product_tmpl_id) | |
| -> Hash Right Join (cost=7994.47..40738.00 rows=41069 width=35) (actual time=149.220..156.777 rows=41069 loops=1) | |
| Hash Cond: (ir_translation.res_id = stock_quant__product_id__product_tmpl_id.id) | |
| -> Unique (cost=0.42..32739.26 rows=163 width=25) (actual time=0.034..119.306 rows=1 loops=1) | |
| -> Index Scan using ir_translation_name_lang_res_id_id_idx on ir_translation (cost=0.42..31908.26 rows=332402 width=25) (actual time=0.033..101.747 rows=360448 loops=1) | |
| Index Cond: (((name)::text = 'product.template,name'::text) AND ((lang)::text = 'fr_FR'::text)) | |
| -> Hash (cost=7480.69..7480.69 rows=41069 width=18) (actual time=29.899..29.899 rows=41069 loops=1) | |
| Buckets: 8192 Batches: 1 Memory Usage: 2084kB | |
| -> Seq Scan on product_template stock_quant__product_id__product_tmpl_id (cost=0.00..7480.69 rows=41069 width=18) (actual time=0.033..23.149 rows=41069 loops=1) | |
| -> Hash (cost=3356.09..3356.09 rows=42014 width=32) (actual time=54.501..54.501 rows=42081 loops=1) | |
| Buckets: 8192 Batches: 1 Memory Usage: 2303kB | |
| -> Hash Left Join (cost=1354.12..3356.09 rows=42014 width=32) (actual time=11.663..46.524 rows=42081 loops=1) | |
| Hash Cond: (stock_quant.product_id = stock_quant__product_id.id) | |
| -> Seq Scan on stock_quant (cost=0.00..1214.21 rows=42014 width=16) (actual time=0.005..7.960 rows=42081 loops=1) | |
| Filter: (location_id = ANY ('{15,19,18,38}'::integer[])) | |
| Rows Removed by Filter: 1 | |
| -> Hash (cost=840.72..840.72 rows=41072 width=16) (actual time=11.646..11.646 rows=41072 loops=1) | |
| Buckets: 8192 Batches: 1 Memory Usage: 1605kB | |
| -> Seq Scan on product_product stock_quant__product_id (cost=0.00..840.72 rows=41072 width=16) (actual time=0.003..5.825 rows=41072 loops=1) | |
| Total runtime: 280.859 ms | |
| (26 rows) | |
| 10e=# -- Cost of translation subquery prep down from ~960ms to ~120ms |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Impressive queries here.. 👍