postgresql SQL analysis using explain

Scan the node

1. Seq Scan: Sequential scan (full table scan)
2. Index Scan: Index Scan (not just return the value of the index column)
3. IndexOnly Scan: Index scan (only returns the value of the index column)
4. BitmapIndex Scan: Scan with Bitmap structure
5. BitmapHeap Scan: Convert the Bitmap structure returned by BitmapIndex Scan into a tuple structure

Bitmap Structure Scan: Processing multipleIndex columnCombination query

Case

Here is a paragraph of executionexplain ANALYZE Print OutResults

Append  (cost=4.32..18016.95 rows=4507 width=54) (actual time=0.037..20.751 rows=6046 loops=1)
  ->  Bitmap Heap Scan on dwa_d_dg_staff_directsale_dev_report_p20231114 a  (cost=4.32..19.11 rows=4 width=49) (actual time=0.036..0.045 rows=4 loops=1)
        Recheck Cond: ((branch_id)::text = '837010040280000'::text)
        Heap Blocks: exact=4
        ->  Bitmap Index Scan on dwa_d_dg_staff_directsale_dev_report_p20231114_branch_id_idx  (cost=0.00..4.32 rows=4 width=0) (actual time=0.025..0.025 rows=4 loops=1)
              Index Cond: ((branch_id)::text = '837010040280000'::text)
  ->  Bitmap Heap Scan on dwa_d_dg_staff_directsale_dev_report_p20231115 a_1  (cost=4.32..19.11 rows=4 width=49) (actual time=0.019..0.026 rows=4 loops=1)
        Recheck Cond: ((branch_id)::text = '837010040280000'::text)
        Heap Blocks: exact=4
        ->  Bitmap Index Scan on dwa_d_dg_staff_directsale_dev_report_p20231115_branch_id_idx  (cost=0.00..4.32 rows=4 width=0) (actual time=0.013..0.013 rows=4 loops=1)
              Index Cond: ((branch_id)::text = '837010040280000'::text)
  ->  Bitmap Heap Scan on dwa_d_dg_staff_directsale_dev_report_p20231116 a_2  (cost=4.54..64.13 rows=16 width=54) (actual time=0.023..0.073 rows=18 loops=1)
        Recheck Cond: ((branch_id)::text = '837010040280000'::text)
        Heap Blocks: exact=18

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

AI writing code

From the results printed above, we can analyze that branch_id is one of the conditions in where. When executing this where condition, branch_id performsIndex Scan, What should I look at specifically? Let’s analyze it in detail below:

1. Bitmap Heap Scan on dwa_d_dg_staff_directsale_dev_report_p20231114: Indicates Bitmap Heap scanning of the dwa_d_dg_staff_directsale_dev_report table
2. Recheck Cond: ((branch_id)::text = ‘837010040280000’::text) indicates that the condition for the Recheck operation of Bitmap Heap Scan is branch_id = ‘837010040280000’
3. Heap Blocks: exact=4 indicates that the number of data blocks accurately scanned is 4
4. Bitmap Index Scan on dwa_d_dg_staff_directsale_dev_report_p20231114_branch_id_idx: means using dwa_d_dg_staff_directsale_dev_report_p20231114_branch_id_idx index for bitmap index scanning
5. actual time: execution time, format is xxx…xxx. Before... is the actual startup time of the node, that is, the time it takes to find the first result that meets the node's conditions, and after... is the actual execution time of the node
6. Filter: Conditional filtering
7. rows: refers to the actual number of rows returned by the node
8. loops: refers to the actual number of restarts of the node. If a planning node changes its relevant parameter values ​​(such as bound variables) during operation, it is necessary to rerun the planning node.
9. width: The average width of each line is 74 bytes
10. cost: The first number indicates the cost of starting, that is, how much cost value is needed to return the first row; the second number indicates the cost of returning all data

Notice

If the proportion of the obtained result set is relatively small, but the number of tuples is large, it may be Bitmap Index ScanperformanceBetter than Index Scan