حذف کارآمد داده ها – انجمن DEV

CREATE DATABASE deletes;
ALTER DATABASE deletes CONFIGURE ZONE USING gc.ttlseconds= 60;
USE deletes;

CREATE TABLE deletes.order_items ( id PRIMARY KEY, customer_id, order_id, product_id, order_date )
AS
SELECT
  g.i AS id,
  g.i % 77777 AS customer_id,
  g.i % 1000000 as order_id,
  g.i % 11111 as product_id,
  g.i::TIMESTAMP as order_date
FROM generate_series(1578003200, 1678003200, 1) g(i);
-- 100,000,000 records
--  between 2020-01-02 22:13:20 (i.e., 1578003200)
--  and 2023-03-05 08:00:00 (i.e., 1678003200)

CREATE INDEX ON order_items ( customer_id );
CREATE INDEX ON order_items ( order_id );
CREATE INDEX ON order_items ( product_id );
CREATE INDEX ON order_items ( order_date );

SELECT
  COUNT(*) AS rec_count,
  COUNT(DISTINCT customer_id) AS cust_count,
  COUNT(DISTINCT order_id) AS ord_count,
  COUNT(DISTINCT product_id) AS prd_count,
  COUNT(DISTINCT order_date) AS date_count
FROM order_items;

  rec_count | cust_count | ord_count | prd_count | date_count
------------+------------+-----------+-----------+-------------
  100000001 |      77777 |   1000000 |     11111 |  100000001

SELECT *
FROM order_items
WHERE id = 1600000000;

      id     | customer_id | order_id | product_id |     order_date
-------------+-------------+----------+------------+----------------------
  1600000000 |       49333 |        0 |       4889 | 2020-09-13 12:26:40

EXPLAIN
SELECT *
FROM order_items
WHERE id = 1600000000;
                                                        info
---------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
    table: order_items@order_items_pkey
    spans: [/1600000000 - /1600000000]

SELECT *
FROM order_items
WHERE order_date = 1600000000::timestamp;
      id     | customer_id | order_id | product_id |     order_date
-------------+-------------+----------+------------+----------------------
  1600000000 |       49333 |        0 |       4889 | 2020-09-13 12:26:40

EXPLAIN
SELECT *
FROM order_items
WHERE order_date = 1600000000::timestamp;
                                                                            info
-------------------------------------------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 1
  │ table: order_items@order_items_pkey
  │
  └── • scan
        estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
        table: order_items@order_items_order_date_idx
        spans: [/'2020-09-13 12:26:40' - /'2020-09-13 12:26:40']

  index recommendations: 1
  1. type: index replacement
     SQL commands: CREATE INDEX ON order_items (order_date) STORING (customer_id, order_id, product_id); DROP INDEX order_items@order_items_order_date_idx;

EXPLAIN
SELECT id, order_date
FROM order_items
WHERE order_date = 1600000000::timestamp;
                                                        info
---------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
    table: order_items@order_items_order_date_idx
    spans: [/'2020-09-13 12:26:40' - /'2020-09-13 12:26:40']

EXPLAIN
DELETE FROM order_items
WHERE id = 1600000000;
                                                          info
-------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • delete
  │ from: order_items
  │ auto commit
  │
  └── • scan
        estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
        table: order_items@order_items_pkey
        spans: [/1600000000 - /1600000000]

EXPLAIN DELETE FROM order_items
WHERE order_date = 1600000000::timestamp;
                                                                            info
-------------------------------------------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • delete
  │ from: order_items
  │ auto commit
  │
  └── • index join
      │ estimated row count: 1
      │ table: order_items@order_items_pkey
      │
      └── • scan
            estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
            table: order_items@order_items_order_date_idx
            spans: [/'2020-09-13 12:26:40' - /'2020-09-13 12:26:40']

  index recommendations: 1
  1. type: index replacement
     SQL commands: CREATE INDEX ON order_items (order_date) STORING (customer_id, order_id, product_id); DROP INDEX order_items@order_items_order_date_idx;

CREATE INDEX ON order_items (order_date) STORING (customer_id, order_id, product_id);
DROP INDEX order_items@order_items_order_date_idx;                                                                                                                         

EXPLAIN DELETE FROM order_items
WHERE order_date = 1600000000::timestamp;                                                                                                  info
-------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • delete
  │ from: order_items
  │ auto commit
  │
  └── • scan
        estimated row count: 1 (<0.01% of the table; stats collected 3 hours ago; using stats forecast for 3 hours ago)
        table: order_items@order_items_order_date_idx1
        spans: [/'2020-09-13 12:26:40' - /'2020-09-13 12:26:40']