Ian Timothy's questions

在 9.6.3 中，在 WHERE 子句中添加 NULLIF() 会将计划从位图堆扫描更改为索引扫描，并将执行时间从大约 0.3 毫秒更改为 43,000 毫秒。

这是一个相关的问题：https ://stackoverflow.com/questions/21062148/how-to-query-postgres-on-optional-params

这是快速查询和计划：

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state as plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers AS c ON (v.customer_id = c.id)
WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX');
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                  QUERY PLAN                                                                   │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=824.67..451863.32 rows=43830 width=228) (actual time=0.202..0.203 rows=1 loops=1)                                          │
│   ->  Bitmap Heap Scan on customers_vehicles v  (cost=824.12..121946.77 rows=43830 width=164) (actual time=0.104..0.104 rows=1 loops=1)       │
│         Recheck Cond: (tag = 'XXXXXX'::text)                                                                                                  │
│         Heap Blocks: exact=1                                                                                                                  │
│         ->  Bitmap Index Scan on customers_vehicles_tag_idx  (cost=0.00..813.16 rows=43830 width=0) (actual time=0.090..0.090 rows=1 loops=1) │
│               Index Cond: (tag = 'XXXXXX'::text)                                                                                              │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..7.52 rows=1 width=128) (actual time=0.093..0.094 rows=1 loops=1)            │
│         Index Cond: (id = v.customer_id)                                                                                                      │
│ Planning time: 0.268 ms                                                                                                                       │
│ Execution time: 0.256 ms                                                                                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

这是缓慢的查询和计划：

SELECT
        c.name_first, c.name_last, c.postal_code,
        v.tag as plate_number, v.tag_state AS plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
        JOIN customers as c on (v.customer_id = c.id)
WHERE (nullif('XXXXXX'::text,'') IS NULL OR v.tag='XXXXXX');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                    QUERY PLAN                                                                                    │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=1.12..7509681.67 rows=87441 width=228) (actual time=5462.111..42209.513 rows=1 loops=1)                                                                       │
│   ->  Index Scan using customers_vehicles_customer_id_idx on customers_vehicles v  (cost=0.56..6921180.68 rows=87441 width=164) (actual time=5462.023..42209.423 rows=1 loops=1) │
│         Filter: ((NULLIF('XXXXXX'::text, ''::text) IS NULL) OR (tag = 'XXXXXX'::text))                                                                                             │
│         Rows Removed by Filter: 8766005                                                                                                                                          │
│   ->  Index Scan using customers_pkey on customers c  (cost=0.56..6.72 rows=1 width=128) (actual time=0.080..0.081 rows=1 loops=1)                                               │
│         Index Cond: (id = v.customer_id)                                                                                                                                         │
│ Planning time: 0.209 ms                                                                                                                                                          │
│ Execution time: 42209.549 ms                                                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

唯一的区别在于 WHERE 子句中的慢查询：'XXXXXX' IS NULL更改为nullif('XXXXXX','') IS NULL. 如果第一个条件包含 ，规划器似乎无法优化它NULLIF()。有没有办法强迫它这样做？

在 PostgreSQL 9.4.1 中，我创建了一个自定义类型my_test_type，并发现执行基本的 INSERTNULL::my_test_type会得到预期的结果，但是NULL::my_test_type作为函数参数并使用完全相同的查询，我得到了意想不到的结果：

CREATE TYPE my_test_type as (part1 text, part2 text);
CREATE TABLE my_test_table (id serial, test_col my_test_type);
CREATE FUNCTION my_test_table_insert (test_arg my_test_type DEFAULT NULL)
  RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO my_test_table (test_col) VALUES (test_arg);
END; $$;

-- gives null as expected
SELECT NULL::my_test_type;

-- test_col value will be null as expected
INSERT INTO my_test_table (test_col) VALUES (NULL);

-- this performs the exact same query above but in a function,
-- but test_col value shows '(,)', which is NOT expected
SELECT my_test_table_insert(NULL);

-- this is what seems to happening in the function, but I don't understand why
INSERT INTO my_test_table(test_col) VALUES (ROW(NULL, NULL)::my_test_type);

\pset null 'NULL'
SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM my_test_table;
┌────┬──────────┬───────┬───────┬─────────┐
│ id │ test_col │ part1 │ part2 │ is_null │
├────┼──────────┼───────┼───────┼─────────┤
│  1 │ NULL     │ NULL  │ NULL  │ t       │
│  2 │ (,)      │ NULL  │ NULL  │ t       │
│  3 │ (,)      │ NULL  │ NULL  │ t       │
└────┴──────────┴───────┴───────┴─────────┘
(3 rows)

即使所有内容都测试为 null，为什么INSERT函数内部的结果在表中显示不同？

编辑：问题可以简化：

VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type);
┌─────────┐
│ column1 │
├─────────┤
│ NULL    │
│ (,)     │
└─────────┘
(2 rows)

在 PostgreSQL 9.2.3 上尝试此操作时：

CREATE EXTENSION btree_gist;

我收到此错误：

错误：无法打开扩展控制文件“/opt/local/share/postgresql92/extension/btree_gist.control”：没有这样的文件或目录

运行SELECT pg_available_extensions();显示确实不可用。

在 PostgreSQL 9.2.3 中，我正在尝试创建这个简化的表：

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);

但我得到这个错误：

ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define
       a default operator class for the data type.

PostgreSQL 文档使用这个对我不起作用的例子：

CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

相同的错误信息。

而这个对我也不起作用：

CREATE TABLE zoo (
    cage   INTEGER,
    animal TEXT,
    EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

相同的错误信息。

我可以毫无问题地创建它：

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (startend WITH &&)
);

和这个：

CREATE TABLE test (
    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING btree (user_id WITH =)
);

我花了相当多的时间寻找有关弄清楚如何使这项工作发挥作用或弄清楚为什么它不起作用的提示。有任何想法吗？