Re: query gone haywire :)
От | Robin Ericsson |
---|---|
Тема | Re: query gone haywire :) |
Дата | |
Msg-id | 1097591883.10712.173.camel@pylver.localhost.nu. обсуждение исходный текст |
Ответ на | Re: query gone haywire :) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
On Fri, 2004-10-08 at 10:11 -0400, Tom Lane wrote: > Robin Ericsson <robin.ericsson@profecta.se> writes: > > -> Index Scan using idx_d_entered on data (cost=0.00..18024.04 rows=50360 width=16) (actual time=0.210..0.247rows=1 loops=1) > > Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp without time zone - '00:01:00'::interval)< entered) > > You're running into the well-known problem that the planner can't make > good estimates for index conditions that involve non-constant terms > (such as CURRENT_TIMESTAMP). Lacking a decent estimate, it guesses that > this scan will produce many more rows than it really will, and so it > tends to favor plans that would be good in that scenario, but are not > optimal for retrieving just a couple of rows. > > One workaround is to do the date arithmetic on the client side; another > is to cheat by hiding the arithmetic in a function like "ago(interval)" > that you lyingly claim is IMMUTABLE. See the pgsql-performance > archives. I did run a new explain analyze on the query and found the attached result. status=# EXPLAIN ANALYZE status-# SELECT status-# data.entered, status-# data.machine_id, status-# datatemplate_intervals.template_id, status-# data_values.value status-# FROM status-# data, data_values, datatemplate_intervals status-# WHERE status-# datatemplate_intervals.id = data_values.template_id AND status-# data_values.data_id = data.id AND status-# data.machine_id IN (2,3) AND status-# current_timestamp::timestamp - interval '60 seconds' < data.entered; It seems very strange that it does a full index scan on idx_dv_data_id. Regards, Robin
Вложения
В списке pgsql-general по дате отправления: