Ian Timothy's questions

Na versão 9.6.3, adicionar NULLIF() na cláusula WHERE altera o plano de uma varredura de heap de bitmap para uma varredura de índice e altera o tempo de execução de cerca de 0,3 ms para 43.000 ms.

Aqui está uma pergunta relacionada: https://stackoverflow.com/questions/21062148/how-to-query-postgres-on-optional-params

Esta é a consulta e o plano rápidos:

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                                                                                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

E esta é a consulta e o plano lentos:

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                                                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

A única diferença está na consulta lenta na cláusula WHERE: 'XXXXXX' IS NULLmuda para nullif('XXXXXX','') IS NULL. Parece que o planejador não pode otimizar a primeira condição se ela contiver NULLIF(). Existe uma maneira de coagi-lo a fazê-lo?

No PostgreSQL 9.4.1, criei um tipo personalizado my_test_typee descobri que fazer um INSERT básico com NULL::my_test_typeo resultado esperado, mas com NULL::my_test_typeum argumento de função e usando exatamente a mesma consulta, obtenho um resultado inesperado:

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)

Mesmo que tudo teste como nulo, por que o resultado de INSERTdentro da função aparece diferente na tabela?

Edit: O problema pode ser simplificado:

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

No PostgreSQL 9.2.3 ao tentar isto:

CREATE EXTENSION btree_gist;

Eu recebo este erro:

ERRO: não foi possível abrir o arquivo de controle de extensão "/opt/local/share/postgresql92/extension/btree_gist.control": Arquivo ou diretório inexistente

A execução SELECT pg_available_extensions();mostra que de fato não está disponível.

No PostgreSQL 9.2.3 estou tentando criar esta tabela simplificada:

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

Mas recebo este erro:

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.

Os documentos do PostgreSQL usam este exemplo que não funciona para mim:

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

Mesma mensagem de erro.

E este , que também não funciona para mim:

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

Mesma mensagem de erro.

Eu sou capaz de criar isso sem nenhum problema:

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

e isto:

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

Passei um bom tempo procurando dicas sobre como fazer isso funcionar, ou descobrir por que não funcionaria. Alguma ideia?