如何避免在主表中进行扫描 [英] How avoid the scan in the main table

问题描述

我有一个表，在几天内使用继承在多个表中进行分区。

I have a table partitioned using inherit in multiple tables for days.

有一个插入触发器可以将数据插入到适当的表中，因此从理论上讲，avl表不应包含任何数据

There is one insert trigger to insert the data to the proper table, so in theory the avl table shouldnt have any data

CREATE OR REPLACE FUNCTION avl_db.avl_insert_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.event_time >= '2017-06-01 00:00:00' AND NEW.event_time < '2017-06-02 00:00:00' ) THEN 
        INSERT INTO avl_db.avl_20170601 VALUES (NEW.*);
    ELSEIF ( NEW.event_time >= '2017-06-02 00:00:00' AND NEW.event_time < '2017-06-03 00:00:00' ) THEN 
        INSERT INTO avl_db.avl_20170602 VALUES (NEW.*);
    ELSEIF ( NEW.event_time >= '2017-06-03 00:00:00' AND NEW.event_time < '2017-06-04 00:00:00' ) THEN 
        INSERT INTO avl_db.avl_20170603 VALUES (NEW.*);
    ELSEIF ( NEW.event_time >= '2017-06-04 00:00:00' AND NEW.event_time < '2017-06-05 00:00:00' ) THEN 
        INSERT INTO avl_db.avl_20170604 VALUES (NEW.*);
    ELSEIF ( NEW.event_time >= '2017-06-05 00:00:00' AND NEW.event_time < '2017-06-06 00:00:00' ) THEN 
        INSERT INTO avl_db.avl_20170605 VALUES (NEW.*);
....
    ELSE
        RAISE EXCEPTION 'Date out of range.';
    END IF;

    RETURN NULL;

每个表都有检查约束和索引，因此只能检查日期正确的表

Every table has a check constraint and index so only check the table with the right date

CONSTRAINT avl_20170605_event_time_check 
CHECK (event_time >= '2017-06-05 00:00:00'::timestamp without time zone 
   AND event_time <  '2017-06-06 00:00:00'::timestamp without time zone)

CREATE INDEX avl_20170605__event_time_idx
  ON avl_db.avl_20170605
  USING btree
  (event_time);

问题是何时使用 event_time 仍要对主 avl 表执行一些操作。

The thing is when do select using the event_time to filter still do some operation over the main avl table.

explain analyze
    SELECT *
    FROM avl_db.avl
    WHERE event_time between '2017-06-05 09:40:44'::timestamp without time zone - '6 minute'::interval
                         AND '2017-06-05 09:40:44'::timestamp without time zone - '1 minute'::interval

您可以看到使用 avl_20170605__event_time_idx 中的索引，而忽略了其余表，但也尝试对<$ c进行Seq扫描$ c> avl 。

You can see use the index from avl_20170605__event_time_idx and ignore the rest of the tables, but also try to do a Seq Scan on avl.

Append  (cost=0.00..720.98 rows=7724 width=16) (actual time=0.044..5.523 rows=7851 loops=1)
  ->  Seq Scan on avl  (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
        Filter: ((event_time >= '2017-06-05 09:34:44'::timestamp without time zone) AND (event_time <= '2017-06-05 09:39:44'::timestamp without time zone))
  ->  Index Scan using avl_20170605__event_time_idx on avl_20170605  (cost=0.42..720.98 rows=7723 width=16) (actual time=0.042..5.110 rows=7851 loops=1)
        Index Cond: ((event_time >= '2017-06-05 09:34:44'::timestamp without time zone) AND (event_time <= '2017-06-05 09:39:44'::timestamp without time zone))
Planning time: 3.050 ms
Execution time: 5.737 ms

我想知道是否有一种优化器的方法停止尝试扫描并追加表 avl 。

I'm wondering if there is a way optimizer stop trying to scan and append the table avl.

推荐答案

对分区表的每次扫描也会扫描（通常为空）父表，因为它没有（而且不能）具有 CHECK 约束，就像子表一样。

Every scan on a partitioned table will also scan the (usually empty) parent table, since it doesn't (and cannot) have a CHECK constraint like the child tables have.

您可以看到此扫描不会对总体查询持续时间。

You can see that this scan doesn't contribute any time to the overall query duration.

这篇关于如何避免在主表中进行扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！