Get best of the week

Friend ORDER BY com índices


Olá Habr!


Estou traduzindo lentamente os artigos de Marcus Vinand a partir do blog index luke.


. , . — , .


. pipelined order by. , , . top-N pipelined order by. , , , .


, : 2017. , , , — . , .


«.», . , PostgreSQL — , , . .



SQL-, order by, , . , . , , where , order by.


.: . : sale_date, ( , ), product_id — , ; sale_date .


, , , :


SELECT sale_date, product_id, quantity
  FROM sales
WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date, product_id

, sale_date where. , order by .


:


DB2
Explain Plan
------------------------------------------------------------
ID | Operation             |                     Rows | Cost
 1 | RETURN                |                          |  682
 2 |  TBSCAN               |     394 of 394 (100.00%) |  682
 3 |   SORT                |     394 of 394 (100.00%) |  682
 4 |    FETCH SALES        |     394 of 394 (100.00%) |  682
 5 |     IXSCAN SALES_DATE | 394 of 1009326 (   .04%) |   19

Predicate Information
  5 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))

- DB2 where : WHERE sale_date = CURRENT_DATE — 1 DAY. .


Oracle
---------------------------------------------------------------
|Id | Operation                    | Name       | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT             |            |  320 |   18 |
| 1 |  SORT ORDER BY               |            |  320 |   18 |
| 2 |   TABLE ACCESS BY INDEX ROWID| SALES      |  320 |   17 |
|*3 |    INDEX RANGE SCAN          | SALES_DATE |  320 |    3 |
---------------------------------------------------------------

PostgreSQL
Sort  (cost=27862.15..28359.74 rows=199034 width=16) (actual time=259.075..370.953 rows=200000 loops=1)
     Sort Key: product_id
     Sort Method: external merge  Disk: 5872kB
     ->  Index Scan using sales_date on sales  (cost=0.42..6942.52 rows=199034 width=16) (actual time=0.030..121.512 rows=200000 loops=1)
         Index Cond: (sale_date = '2020-01-05 00:00:00'::timestamp without time zone)
Planning Time: 0.226 ms
Execution Time: 458.083 ms

index range scan , . , :


DROP INDEX sales_date

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id)

SELECT sale_date, product_id, quantity
  FROM sales
WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date, product_id

:


DB2
Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  688
 2 |  FETCH SALES         |     394 of 394 (100.00%) |  688
 3 |   IXSCAN SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
  3 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))

Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  300 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

PostgreSQL
Index Scan using sales_dt_pr on sales  (cost=0.42..18413.96 rows=199034 width=16) (actual time=0.043..206.096 rows=200000 loops=1)
    Index Cond: (sale_date = '2020-01-05 00:00:00'::timestamp without time zone)
Planning Time: 0.259 ms
Execution Time: 295.872 ms

, sort order by , order by. .



, (.: Oracle), . , (cost value) (.: , ).


.: , .


Oracle ROWID, . , , ROWID , . , , , . , .


ROWID. , .


, , , - . , , , , — . sale_dt_pr. , , .


, order by. , , product_id:


SELECT sale_date, product_id, quantity
  FROM sales
WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY product_id

, product_id — (.: ). , order by , .




.: . , . , ( , ) . , (product_id ) , (, -1 5 2 ). - .


. , where :


SELECT sale_date, product_id, quantity
  FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY product_id

, , . , . , product_id (.: , , id - , — , ). , , product_id. , , , order by.


:


DB2
Explain Plan
-------------------------------------------------------------
ID | Operation              |                     Rows | Cost
 1 | RETURN                 |                          |  688
 2 |  TBSCAN                |     394 of 394 (100.00%) |  688
 3 |   SORT                 |     394 of 394 (100.00%) |  688
 4 |    FETCH SALES         |     394 of 394 (100.00%) |  688
 5 |     IXSCAN SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
  5 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)

Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  301 |
| 1 | SORT ORDER BY               |             |  320 |  301 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

PostgreSQL
Sort  (cost=34388.50..34886.08 rows=199034 width=16) (actual time=258.029..367.186 rows=200001 loops=1)
    Sort Key: product_id
    Sort Method: external merge  Disk: 5872kB
    ->  Bitmap Heap Scan on sales  (cost=4610.94..13468.86 rows=199034 width=16) (actual time=19.947..122.290 rows=200001 loops=1)
        Recheck Cond: (sale_date >= '2020-01-05 00:00:00'::timestamp without time zone)
        Heap Blocks: exact=1275
        ->  Bitmap Index Scan on sales_dt_pr  (cost=0.00..4561.18 rows=199034 width=0) (actual time=19.742..19.742 rows=200001 loops=1)
            Index Cond: (sale_date >= '2020-01-05 00:00:00'::timestamp without time zone)
Planning Time: 0.141 ms
Execution Time: 452.337 ms

?


, (pipeline execution), :


  1. , .
  2. order by.

, - , — order by . , . , , , - . , order by.


, . order by . , order by. .



, , . , , . , , , , N , , , , , . top-N .



: Markus Winand

Source: https://habr.com/ru/post/undefined/

More articles:


All Articles