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.
Abraços
--
Flávio Alves Granato
gpg: 968F:A938:70B9:82C7:5198:2C74:13CB:2C25:EF1E:726D
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
