Re: help with too slow query

2012/11/6 Pedro Jiménez Pérez <p.jimenez@ismsolar.com>
> Ok, here we go:
>
> I'm using postgresql version 8.0
>
> Here is my query that is too slow:  http://explain.depesz.com/s/GbQ


Well, I would start with a note, that 8.0 is not supported anymore:
http://www.postgresql.org/support/versioning/
Please, consider upgrading your instance.

Also, it is not handy to provide schema details here and anonymize the
EXPLAIN output.
Here's the visualization of your initial plan: http://explain.depesz.com/s/AOAN

The following join: (ism_floatvalues.id_signal = ism_signal.id_signal)
is wrongly estimated by the planner (row 3 of the above explain visualization).
It looks like NestedLoop join with IndexScan over
ism_floatvalues_index_idsignal_timestamp
might do a better job.

Try the following:
ALTER TABLE ism_floatvalues ALTER COLUMN id_signal SET STATISTICS
1000; /* 1000 is maximum for 8.0 */
ANALYZE ism_floatvalues;

Let me know if it helps.


--
Victor Y. Yegorov