Christian Schmitt's questions

Tenho dificuldade em entender duas limitações do postgres quando se trata de criar índices e excluí-los no modo concorrente:

o primeiro sobre CREATE INDEX CONCURRENTLY:

As compilações de índice regulares permitem que outras compilações de índice regulares na mesma tabela ocorram simultaneamente, mas somente uma compilação de índice simultânea pode ocorrer em uma tabela por vez.

na verdade eu tentei isso, mas eu poderia aplicar dois comandos com CREATE INDEX CONCURRENTLYe ambos os índices eram válidos. Isso significa apenas que o Postgres permite executar o comando duas vezes, mas

o segundo sobre DROP INDEX CONCURRENTLY:

Há várias ressalvas a serem observadas ao usar esta opção. Apenas um nome de índice pode ser especificado, e a opção CASCADE não é suportada. (Assim, um índice que suporta uma restrição UNIQUE ou PRIMARY KEY não pode ser descartado desta forma.)

atualmente não li os documentos e removi um índice com DROP INDEX CONCURRENTLYisso era UNIQUE. No entanto, funcionou e o índice foi removido, esqueci de algo aqui?

atualmente tenho a seguinte tabela:

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL,
  other_key TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

Agora eu quero agrupar pela consulta assim:

SELECT other_key, SUM(quantity) FROM demo GROUP BY other_key;       

Isso funciona até agora tudo bem, porém agora eu quero filtrar pela chave e também imprimir o mais recente datepara a tabela, existe uma boa maneira de fazer isso?

Pseudo (falhará porque a chave não está agrupada por)

SELECT other_key, SUM(quantity), MAX(date) FROM demo GROUP BY other_key WHERE key = ?;    

minha ideia inicial é uma subconsulta:

SELECT other_key, SUM(quantity), MAX(date) FROM (SELECT * FROM demo WHERE key = ?) GROUP BY other_key;

Existe uma maneira melhor de fazer isso? E qual seria um bom índice para a tabela?

Meu índice atual é:

CREATE INDEX demo_all_idx ON demo (key, other_key, quantity);

Bônus (editado):

• Existe alguma maneira de classificar pelo MAX (data) então?

• Existe uma maneira de criar uma função agregada que obtenha a data mais antiga em que a quantidade é maior que zero? ou seja, algum tipo de armazenamento de evento de inventário onde a data mais recente não deve ser a última entrada feita e, em vez disso, a mais recente entrada feita onde a quantidade não é subtraída / zero? como considere a seguinte tabela:

  id | key     | other_key | quantity | date
  ---+---------+-----------+----------+-----------------------
   6 | 0A19882 | 01/01     |      100 | 2016-08-30 00:00:00+02
   7 | 0A19882 | 01/02     |      -50 | 2016-09-01 00:00:00+02
   8 | 0A19882 | 01/01     |      100 | 2016-09-02 00:00:00+02
   9 | 0A19882 | 01/02     |      100 | 2016-08-31 00:00:00+02
  11 | 0A19882 | 01/03     |      100 | 2016-08-31 00:00:00+02
  12 | 0A19882 | 01/03     |     -100 | 2016-09-02 00:00:00+02
  13 | 0A19882 | 01/03     |      100 | 2016-09-04 00:00:00+02
  

A data para 01/01 deveria ser 2016-08-30 00:00:00+02enquanto para 01/03 deveria ser 2016-09-04 00:00:00+02pois o evento com id 12 chegou a zero.

Existe alguma maneira de ter uma LIKEconsulta Postgres em um campo ARRAY?

Atualmente eu quero algo assim:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Atualmente mais baixo não é necessário tanto. No entanto, deve encontrar UM campo correspondente dentro do ARRAY. É mesmo possível?

Atualmente eu uso uma visão materializada para gerar a tabela "list" com um JOIN e um ARRAY_AGG(), já que eu JOIN uma tabela onde mais valores poderiam estar na tabela certa. O que duplicaria os campos da tabela à esquerda, o que não é o que eu quero.

Edite é assim que eu crio a visão (realmente lenta e feia):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id, 
       a.oa_nr, 
       a.date_deleted, 
       a.lock, 
       a.sds_nr, 
       a.kd_art_nr, 
       a.kd_art_index, 
       a.kd_art_extend, 
       a.surface, 
       a.execution, 
       a.surface_area, 
       a.cu_thickness, 
       a.endintensity, 
       a.drilling, 
       array_agg(o.id::text) AS offer_list 
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr 
GROUP BY .....;  

Também preciso retornar os IDs da task_offertabela.

Atualmente, tenho uma visão que é definida assim:

                       View "public.customer_list"
  Column   |          Type           | Modifiers | Storage  | Description 
-----------+-------------------------+-----------+----------+-------------
 id        | bigint                  |           | plain    | 
 name      | character varying(100)  |           | extended | 
 street    | character varying(100)  |           | extended | 
 zip       | character varying(10)   |           | extended | 
 city      | character varying(100)  |           | extended | 
 country   | character varying(3)    |           | extended | 
 phone     | character varying(100)  |           | extended | 
 mail      | character varying(100)  |           | extended | 
 rating    | integer                 |           | plain    | 
 salesnote | character varying(1800) |           | extended | 
View definition:
 SELECT c.id,
    c.name,
    a.street,
    a.zip,
    a.city,
    a.country,
    c.phone,
    c.mail,
    c.rating,
    c.salesnote
   FROM crm_customer c
     JOIN crm_address a ON a.id = c.address_id;

Para melhor pesquisar na Lista criei um índice com o seguinte:

CREATE INDEX crm_customer_big_index 
    ON crm_customer (name ASC, 
                     name text_pattern_ops, 
                     (id::text) text_pattern_ops, 
                     phone text_pattern_ops, 
                     mail text_pattern_ops, 
                     rating);

Eu pesquiso através desta tabela com uma consulta variável onde, na maioria das vezes, se parece com isso:

SELECT * 
  FROM customer_list
 WHERE lower(name::text) LIKE 'env%' 
       AND rating = 3 
 ORDER BY name ASC 
 LIMIT 20 
 OFFSET 0;

Mas ainda assim meu analisador nunca usará um índice. Existe uma maneira de usar um?

             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.244 rows=1 loops=1)
   ->  Sort  (cost=164.04..164.08 rows=15 width=129) (actual time=3.243..3.243 rows=1 loops=1)
         Sort Key: c.name
         Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=0.28..163.75 rows=15 width=129) (actual time=0.020..3.225 rows=1 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..111.13 rows=15 width=98) (actual time=0.012..3.216 rows=1 loops=1)
                     Filter: ((rating = 3) AND (lower((name)::text) ~~ 'env%'::text))
                     Rows Removed by Filter: 2978
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=1)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.580 ms
 Execution time: 3.286 ms

Atualmente, a consulta ainda é "um tanto" rápida. No entanto, eu poderia ter muito mais dados dentro da minha tabela. Além disso, crm_addresstem um índice sobre street, zip, city, country, que funciona bem.

atualmente esta consulta não usa um índice mesmo depois que eu criei um:

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.307..11.311 rows=20 loops=1)
   ->  Sort  (cost=149.14..149.18 rows=15 width=1156) (actual time=11.305..11.306 rows=20 loops=1)
         Sort Key: c.name
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=0.28..148.85 rows=15 width=1156) (actual time=0.022..6.403 rows=2977 loops=1)
               ->  Seq Scan on crm_customer c  (cost=0.00..96.24 rows=15 width=674) (actual time=0.009..1.184 rows=2977 loops=1)
                     Filter: (rating = 3)
                     Rows Removed by Filter: 2
               ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.001..0.001 rows=1 loops=2977)
                     Index Cond: (id = c.address_id)
                     Heap Fetches: 0
 Planning time: 0.246 ms
 Execution time: 11.353 ms
(13 rows)

Ao desligar o seqscan, set enable_seqscan = false;ele será executado corretamente (ordenado por nome e muito mais rápido):

EXPLAIN ANALYZE SELECT * FROM customer_list WHERE rating = 3 ORDER BY name ASC LIMIT 20 ;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.028..0.103 rows=20 loops=1)
   ->  Nested Loop  (cost=0.56..413.02 rows=15 width=1156) (actual time=0.027..0.101 rows=20 loops=1)
         ->  Index Scan using crm_customer_name on crm_customer c  (cost=0.28..360.41 rows=15 width=674) (actual time=0.018..0.040 rows=20 loops=1)
               Filter: (rating = 3)
               Rows Removed by Filter: 2
         ->  Index Only Scan using crm_address_search_index on crm_address a  (cost=0.28..3.50 rows=1 width=498) (actual time=0.002..0.002 rows=1 loops=20)
               Index Cond: (id = c.address_id)
               Heap Fetches: 0
 Planning time: 0.190 ms
 Execution time: 0.133 ms
(10 rows)

Meu índice real é criado com: CREATE INDEX crm_customer_name_search_index ON crm_customer (name text_pattern_ops ASC NULLS FIRST );

atualmente eu tinha um índice maior e pensei que esse fosse o problema, mas no entanto não é. (classificação de indexação, também não ajuda)

É possível consultar a reltuplescoluna de uma determinada tabela com condições adicionais como table.name LIKE 'hello%'?

Atualmente em minhas tabelas maiores a SELECT count(*)consulta demora muito e não precisaria de uma contagem exata. Então, eu queria saber se é possível adicionar WHEREcláusulas ao reltuplestambém?