Re: help with too slow query

@Victor,

Is the reason of the wrong cardinality estimations of the join indeed due to wrong statistics? I thought that the full table scan was due to the index on the timefield couldn't be used with this predicate:

time_stamp > date_trunc('month', current_date - interval '11 months')

It seems to me that a deterministic FBI should be made of this, deviding the records into month chuncks. Sort of a patch in stead of using partitions. But I'm new to Postgresql, so correct me if i'm wrong,

Regards,
Willem Leenen
Oracle DBA

> Date: Tue, 6 Nov 2012 14:17:07 +0200
> Subject: Re: [PERFORM] help with too slow query
> From: vyegorov(at)gmail(dot)com
> To: p(dot)jimenez(at)ismsolar(dot)com
> CC: pgsql-performance(at)postgresql(dot)org
>
> 2012/11/6 Pedro Jiménez Pérez <p(dot)jimenez(at)ismsolar(dot)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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance