Skip to content

Instantly share code, notes, and snippets.

@moylop260

moylop260/result_explain.md

Last active September 26, 2020 21:14
Show Gist options
  • Save moylop260/16381227fd3bd87e672e531b5a71d918 to your computer and use it in GitHub Desktop.
Save moylop260/16381227fd3bd87e672e531b5a71d918 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
result_explain.md 
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(id)                                                                                                                                                      
FROM "product_template"
LEFT JOIN (SELECT res_id, value
    FROM "ir_translation"
    WHERE type='model' AND name='product.template,name' AND lang='es_CR' AND value!='') as "product_template__name"                                                                                               
    ON ("product_template"."id" = "product_template__name"."res_id") WHERE (("product_template"."active" = true) AND ("product_template"."type" in ('consu','product')))

Result on:

  • PostgreSQL 10.11 (Debian 10.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
Aggregate  (cost=7070.47..7070.48 rows=1 width=8) (actual time=57.825..57.825 rows=1 loops=1)
   Output: count(product_template.id)
   Buffers: shared hit=3580
   ->  Hash Left Join  (cost=4752.32..6986.29 rows=33671 width=4) (actual time=31.018..55.191 rows=33709 loops=1)
         Output: product_template.id
         Inner Unique: true
         Hash Cond: (product_template.id = ir_translation.res_id)
         Buffers: shared hit=3580
         ->  Seq Scan on public.product_template  (cost=0.00..2145.57 rows=33671 width=4) (actual time=0.016..15.898 rows=33709 loops=1)
               Output: product_template.id, product_template.create_uid, product_template.create_date, product_template.write_date, product_template.write_uid, product_template.supply_method, product_template
.uos_id, product_template.list_price, product_template.weight, product_template.mes_type, product_template.uom_id, product_template.description_purchase, product_template.uos_coeff, product_template.purchase_
ok, product_template.company_id, product_template.name, product_template.state, product_template.loc_rack, product_template.uom_po_id, product_template.type, product_template.description, product_template.loc
_row, product_template.description_sale, product_template.procure_method, product_template.rental, product_template.sale_ok, product_template.sale_delay, product_template.loc_case, product_template.produce_de
lay, product_template.categ_id, product_template.volume, product_template.active, product_template.color, product_template.track_incoming, product_template.track_outgoing, product_template.track_all, product_
template.track_production, product_template.sale_line_warn, product_template.sale_line_warn_msg, product_template.purchase_line_warn, product_template.purchase_line_warn_msg, product_template.sequence, produc
t_template.invoice_policy, product_template.service_type, product_template.description_picking, product_template.tracking, product_template.recurring_invoice, product_template.purchase_method, product_templat
e.purchase_requisition, product_template.default_code, product_template.expense_policy, product_template.location_id, product_template.warehouse_id, product_template.hs_code, product_template.responsible_id,
product_template.description_pickingout, product_template.description_pickingin, product_template.subscription_template_id, product_template.service_tracking, product_template.message_main_attachment_id, prod
uct_template.service_to_purchase, product_template.l10n_cr_uom_id, product_template.l10n_cr_tariff_heading
               Filter: (product_template.active AND ((product_template.type)::text = ANY ('{consu,product}'::text[])))
               Rows Removed by Filter: 1297
               Buffers: shared hit=1708
         ->  Hash  (cost=4452.63..4452.63 rows=23975 width=4) (actual time=30.815..30.816 rows=33293 loops=1)
               Output: ir_translation.res_id
               Buckets: 65536 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 1683kB
               Buffers: shared hit=1872
               ->  Bitmap Heap Scan on public.ir_translation  (cost=1126.11..4452.63 rows=23975 width=4) (actual time=12.151..24.604 rows=33293 loops=1)
                     Output: ir_translation.res_id
                     Recheck Cond: (((ir_translation.name)::text = 'product.template,name'::text) AND ((ir_translation.lang)::text = 'es_CR'::text) AND ((ir_translation.type)::text = 'model'::text))
                     Filter: (ir_translation.value <> ''::text)
                     Heap Blocks: exact=1632
                     Buffers: shared hit=1872
                     ->  Bitmap Index Scan on ir_translation_ltn  (cost=0.00..1120.12 rows=23976 width=0) (actual time=11.997..11.997 rows=33293 loops=1)
                           Index Cond: (((ir_translation.name)::text = 'product.template,name'::text) AND ((ir_translation.lang)::text = 'es_CR'::text) AND ((ir_translation.type)::text = 'model'::text))
                           Buffers: shared hit=240
 Planning time: 1.030 ms
 Execution time: 58.056 ms

Result on:

  • PostgreSQL 9.6.13 on x86_64-pc-linux-gnu (Ubuntu 9.6.13-1.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit
 Aggregate  (cost=4349.75..4349.76 rows=1 width=8) (actual time=50731.148..50731.148 rows=1 loops=1)
   Output: count(product_template.id)
   Buffers: shared hit=29029
   ->  Nested Loop Left Join  (cost=0.42..4348.87 rows=354 width=4) (actual time=28.294..50726.385 rows=33716 loops=1)
         Output: product_template.id
         Inner Unique: true
         Join Filter: (product_template.id = ir_translation.res_id)
         Rows Removed by Join Filter: 576633998
         Buffers: shared hit=29029
         ->  Seq Scan on public.product_template  (cost=0.00..4335.11 rows=354 width=4) (actual time=0.148..37.473 rows=33716 loops=1)
               Output: product_template.id, product_template.create_uid, product_template.create_date, product_template.write_date, product_template.write_uid, product_template.supply_method, product_template.uos_id, product_template.list_price, product_template.weight, p$oduct_template.mes_type, product_template.uom_id, product_template.description_purchase, product_template.uos_coeff, product_template.purchase_ok, product_template.company_id, product_template.name, product_template.state, product_template.loc_rack, product_template.uom_p$_id, product_template.type, product_template.description, product_template.loc_row, product_template.description_sale, product_template.procure_method, product_template.rental, product_template.sale_ok, product_template.sale_delay, product_template.loc_case, product_templ$te.produce_delay, product_template.categ_id, product_template.volume, product_template.active, product_template.color, product_template.track_incoming, product_template.track_outgoing, product_template.track_all, product_template.track_production, product_template.sale_li$e_warn, product_template.sale_line_warn_msg, product_template.purchase_line_warn, product_template.purchase_line_warn_msg, product_template.sequence, product_template.invoice_policy, product_template.service_type, product_template.description_picking, product_template.tra$king, product_template.recurring_invoice, product_template.purchase_method, product_template.purchase_requisition, product_template.default_code, product_template.expense_policy, product_template.location_id, product_template.warehouse_id, product_template.hs_code, produc$_template.responsible_id, product_template.description_pickingout, product_template.description_pickingin, product_template.subscription_template_id, product_template.service_tracking, product_template.message_main_attachment_id, product_template.service_to_purchase, prod$ct_template.l10n_cr_uom_id, product_template.l10n_cr_tariff_heading, product_template.life_time, product_template.use_time, product_template.removal_time, product_template.alert_time, product_template.landed_cost_ok, product_template.split_method
               Filter: (product_template.active AND ((product_template.type)::text = ANY ('{consu,product}'::text[])))
               Rows Removed by Filter: 1298
               Buffers: shared hit=3451
         ->  Materialize  (cost=0.42..8.45 rows=1 width=4) (actual time=0.000..0.571 rows=17104 loops=33716)
               Output: ir_translation.res_id
               Buffers: shared hit=25578
               ->  Index Scan using ir_translation_unique on public.ir_translation  (cost=0.42..8.44 rows=1 width=4) (actual time=0.061..22.028 rows=33302 loops=1)
                     Output: ir_translation.res_id
                     Index Cond: (((ir_translation.type)::text = 'model'::text) AND ((ir_translation.name)::text = 'product.template,name'::text) AND ((ir_translation.lang)::text = 'es_CR'::text))
                     Filter: (ir_translation.value <> ''::text)
                     Buffers: shared hit=25578
 Planning time: 1.786 ms
 Execution time: 50731.462 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment