|
@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: help with too slow query > From: vyegorov@xxxxxxxxx > To: p.jimenez@xxxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > > 2012/11/6 Pedro Jiménez Pérez <p.jimenez@xxxxxxxxxxxx> > > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance |
