Pessoal,
Estou com um problema,
Tenho uma tabela message que tem indice nos campos created_date, status,
origin, destination
Na queri abaixo o explain utiliza o indice
SELECT id,
status,
content_id,
substring(content from '"maximumDeliveryDate":\"(.*?)\",')::DATE
AS maximum_delivery_date,
substring(content from '"customerId":(.*?),') AS customer_id,
substring(content from '"method":"(.*?)"') AS method,
substring(content from '"createdDate":\"(.*?)\",') AS
created_date_message,
substring(content from '"placeId":"(.*?)",') AS placeId,
created_date
FROM message
WHERE message_type_id IN ('ITEM_PRICE')
AND status IN ('DONE', 'INITIAL')
AND origin = 'ATG'
AND destination = 'BUS'
AND created_date >= '2014-11-12 06:00'
ORDER BY maximum_delivery_date ASC;
"Sort (cost=860357.09..860357.18 rows=33 width=488)"
" Sort Key: (("substring"(content,
'"maximumDeliveryDate":\"(.*?)\",'::text))::date)"
" -> Index Scan using
message_message_type_id_created_date_status_origin_destinat_idx on
message (cost=0.00..860356.26 rows=33 width=488)"
" Index Cond: (((message_type_id)::text = 'ITEM_PRICE'::text) AND
(created_date >= '2014-11-12 06:00:00-02'::timestamp with time zone) AND
((status)::text = ANY ('{DONE,INITIAL}'::text[])) AND ((origin)::text =
'ATG'::text) AND ((destination)::text = (...)"
Mas quando adicion mais um status na cláusula IN o postgres não utiliza
o indice
SELECT id,
status,
content_id,
substring(content from '"maximumDeliveryDate":\"(.*?)\",')::DATE
AS maximum_delivery_date,
substring(content from '"customerId":(.*?),') AS customer_id,
substring(content from '"method":"(.*?)"') AS method,
substring(content from '"createdDate":\"(.*?)\",') AS
created_date_message,
substring(content from '"placeId":"(.*?)",') AS placeId,
created_date
FROM message
WHERE message_type_id IN ('ITEM_PRICE')
AND status IN ('DONE', 'INITIAL', 'PROCESSED')
AND origin = 'ATG'
AND destination = 'BUS'
AND created_date >= '2014-11-12 06:00'
ORDER BY maximum_delivery_date ASC
"Sort (cost=901932.88..901932.96 rows=33 width=488)"
" Sort Key: (("substring"(content,
'"maximumDeliveryDate":\"(.*?)\",'::text))::date)"
" -> Seq Scan on message (cost=0.00..901932.05 rows=33 width=488)"
" Filter: ((created_date >= '2014-11-12 06:00:00-02'::timestamp
with time zone) AND ((message_type_id)::text = 'ITEM_PRICE'::text) AND
((origin)::text = 'ATG'::text) AND ((destination)::text = 'BUS'::text)
AND ((status)::text = ANY ('{DONE,INITIAL,PR (...)"
Alguém poderia me orientar quanto a este comportamento? Minhas queries
estão demorando muito por causa dele.
Depende basicamente de dois fatores:
1) Versão do PostgreSQL (porque esse comportamento foi alterado no
planejador).
2) Seletividade da coluna status (por exemplo, se os valores possíveis
dentro dos parênteses cobrem grande parte da tabela, o índice não será
usado).
Se puder nos responder às duas questões acima, talvez possamos ajudar.
Sugiro também tentar mudar por comparações mais simples:
Tente mudar de:
AND status IN ('DONE', 'INITIAL', 'PROCESSED')
Para
AND (status = 'DONE' OR status = 'INITIAL' OR status = 'PROCESSED')
E mande-nos o explain de novo.
[]s
Flavio Gurgel
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
