KadekM's questions

A seguinte consulta com função não determinística produz duas mesmas linhas - o CTE é calculado e reutilizado.

WITH foo AS (SELECT uuid_generate_v4() AS id)
SELECT id FROM foo
UNION ALL
SELECT id FROM foo

| id 
| 741f4f69-416b-4b4c-9226-559527f4a84e
| 741f4f69-416b-4b4c-9226-559527f4a84e

Essa execução é garantida? O postgres pode decidir inline-lo para

SELECT id FROM (SELECT uuid_generate_v4() AS id) t
UNION ALL
SELECT id FROM (SELECT uuid_generate_v4() AS id) t

| id 
| 262f0006-b4d5-440a-a86a-d79cd2684458
| c24b5835-9c5c-4d6f-b649-8e510129015b

que produz dois ids diferentes (e, portanto, resultados diferentes)?

O que o padrão SQL especifica? O Postgres oferece alguma garantia adicional? Se não for garantido, qual é a maneira padrão de reutilizar a instrução que depende de procedimentos não determinísticos?

Isso obviamente resulta:

SELECT *, uuid_generate_v4() FROM fruits;

apple      |f6b9a8a5-31c9-4917-9649-f366b7c12492
orange     |a55da08f-95a0-4f33-b536-866a3eae71e9

eu sei que eu poderia fazer

SELECT * FROM fruits LEFT OUTER JOIN uuid_generate_v4() ON TRUE;

Mas existe alguma maneira melhor de atribuir o mesmo uuid? Estou mais interessado no nível conceitual, a função uuid() é apenas um exemplo.

Existe uma maneira de implementar a operação (vamos anotá-la ∘) que se comporta como junção cruzada regular, exceto para conjuntos vazios, ela retorna o lado não vazio.

∘ - new operation
x - cross join
{} - empty result set


A ∘ B  = A x B
A ∘ {} = A
{} ∘ B = B
{} ∘ {} = {}

As colunas de A e B diferem e não estão vazias.

Tenho as seguintes colunas:

key | value
-----------
foo | 1
bar | 2

Existe uma maneira recomendada de transformar isso em hstore?

{foo => 1, bar => 2}

Com base no esquema Northwind :

Suponha que temos uma consulta como esta (desconsidere a utilidade real disso, pois o caso de uso real é mais envolvido)

SELECT unnest(r.agg) FROM (
  SELECT array_agg(x) AS agg
  FROM (
     SELECT *
     FROM northwind.u_suppliers s LEFT JOIN northwind.u_products p ON s.guid = p.supplier
     ) x
  GROUP BY x.supplier
  ) r LIMIT 1;

o resultado é

(9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,22,"Dirk Luchte","Accounting Manager",6j7qZ3Pjad5irKAM1MKX1WU7hvkgIOhZoe9OeRfTX4Q,"(12345) 1210",,northwindSupplier,jp9VvzFBbklHelD_ybS1oDsJbILda1duNhMtCdGBZqg,0,do4_k8zCsOog5tYROlsouF81Uj9I5LbTFawvuPw9Q-0,"2017-10-11 08:45:14.982",47,"Zaanse koeken",9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,AkOUoKYOpHFWIkNcoPX3CLtTl4n_ACN6TwWxqd3gzYQ,"10 - 4 oz boxes",9.5,36,0,0,f,,northwindProduct)

que é um recorde.

Qual seria a maneira de criar uma tabela a partir disso com todas as colunas e registros não aninhados como linhas?

Então haveria:

u_suppliers_column1 | ... | u_suppliers.columnN | u_products.column1 | ... | u_products.columnN
------------------------------------------------------------------------------------------------
9Y_03og...          | 22 | "Dirk Luchte" ...

O trecho é apenas para fins ilustrativos - é claro, criar agregado apenas para desagrupá-lo novamente não é sensato nessas circunstâncias, mas por questão de simplicidade e reprodutibilidade, decidi escrevê-lo como tal.

Eu tenho uma consulta como esta:

SELECT t0.id
FROM platform_conversations t0
LEFT OUTER JOIN contacts t1 ON t1.id = t0.contact_id
WHERE t0.user_id = 5340
AND (
     t0.participant ILIKE '%baa%'  -- (1)
     OR t1.first_name ILIKE '%baa%' -- (2)
     )
LIMIT 50;

e

CREATE INDEX ix_conversations_participant
  ON platform_conversations USING GIN (participant gin_trgm_ops);

CREATE INDEX ix_trgm_contacts_search
  ON contacts USING GIN (first_name gin_trgm_ops);

E não consigo descobrir por que os índices não são usados ​​com ORcondição. Se eu usar apenas (1), ou apenas (2), ou usar ANDem vez disso, ambos serão usados.

Aqui está o plano:

Por apenas (1)

Limit  (cost=12.43..203.68 rows=50 width=37) (actual time=0.037..0.037 rows=0 loops=1)
  ->  Bitmap Heap Scan on platform_conversations t0  (cost=12.43..222.80 rows=55 width=37) (actual time=0.030..0.030 rows=0 loops=1)
        Recheck Cond: ((participant)::text ~~* '%baa%'::text)
        Filter: (user_id = 5340)
        ->  Bitmap Index Scan on ix_conversations_participant  (cost=0.00..12.42 rows=55 width=0) (actual time=0.012..0.012 rows=0 loops=1)
              Index Cond: ((participant)::text ~~* '%baa%'::text)
Planning time: 0.397 ms
Execution time: 0.092 ms

Por apenas (2)

Limit  (cost=16.71..446.06 rows=50 width=37) (actual time=0.034..0.034 rows=0 loops=1)
  ->  Nested Loop  (cost=16.71..471.82 rows=53 width=37) (actual time=0.028..0.028 rows=0 loops=1)
        ->  Bitmap Heap Scan on contacts t1  (cost=16.29..158.99 rows=37 width=37) (actual time=0.022..0.022 rows=0 loops=1)
              Recheck Cond: ((first_name)::text ~~* '%baa%'::text)
              ->  Bitmap Index Scan on ix_trgm_contacts_search  (cost=0.00..16.28 rows=37 width=0) (actual time=0.016..0.016 rows=0 loops=1)
                    Index Cond: ((first_name)::text ~~* '%baa%'::text)
        ->  Index Scan using ix_platform_conversations_contact_id on platform_conversations t0  (cost=0.42..8.45 rows=1 width=74) (never executed)
              Index Cond: ((contact_id)::text = (t1.id)::text)
              Filter: (user_id = 5340)
Planning time: 0.840 ms
Execution time: 0.121 ms

Um lento (com OR):

Limit  (cost=25771.43..47419.63 rows=50 width=37) (actual time=6652.292..6652.292 rows=0 loops=1)
  ->  Hash Left Join  (cost=25771.43..72531.55 rows=108 width=37) (actual time=6652.282..6652.282 rows=0 loops=1)
        Hash Cond: ((t0.contact_id)::text = (t1.id)::text)
        Filter: (((t0.participant)::text ~~* '%baa%'::text) OR ((t1.first_name)::text ~~* '%baa%'::text))
        Rows Removed by Filter: 553477
        ->  Seq Scan on platform_conversations t0  (cost=0.00..20627.29 rows=553512 width=87) (actual time=0.045..1741.899 rows=553477 loops=1)
              Filter: (user_id = 5340)
              Rows Removed by Filter: 386
        ->  Hash  (cost=17578.19..17578.19 rows=384819 width=46) (actual time=2372.508..2372.508 rows=384819 loops=1)
              Buckets: 65536  Batches: 16  Memory Usage: 2359kB
              ->  Seq Scan on contacts t1  (cost=0.00..17578.19 rows=384819 width=46) (actual time=0.014..1168.218 rows=384819 loops=1)
Planning time: 0.741 ms
Execution time: 6652.333 ms

Não pode apenas fazer varreduras de índice de bitmap em t1, t2 e, em seguida, ORs de bitmap neles? Por que o OR é um problema tão grande?

Postgres 9.6 (não é possível criar tag, não há representante suficiente)