|
@Victor,
Spasibo for the information, seems valid to me. Regards, Willem Leenen
> Date: Tue, 6 Nov 2012 16:52:01 +0200 > Subject: Re: help with too slow query > From: vyegorov@xxxxxxxxx > To: willem_leenen@xxxxxxxxxxx > CC: p.jimenez@xxxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx > > 2012/11/6 Willem Leenen <willem_leenen@xxxxxxxxxxx>: > > @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, > > In 8.0, default_statistics_target=10, which means 1e8 rows big table > will get only 10 ranges > for the histograms, a bit too low to get a proper guess on the data > distribution. I would also > have increased default_statistics_target instance-wide, up to 50 at least. > > PostgreSQL can use the index as it is and apply a filter afterwards > for each record emited by > the index scan. Very rough estimate shows, that there'll be round 4.2k > rows for each id_signal > in the ism_floatvalues tables. So index scan looks valid here with the > given setup. > > With increased statistics target for the column I hope optimizer will > do a more precise estimate on > the column selectivity and will prefer to do a NestedLoop join between > ism_signal and ism_floatvalues tables. > > I haven't considered the FBI though. > > I hope I'm not mistaken here, waiting for the OP to provide more input. > > > -- > 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 |
