> Date: Tue, 6 Nov 2012 16:52:01 +0200
> Subject: Re: [PERFORM] help with too slow query
> From: vyegorov@gmail.com
> To: willem_leenen@hotmail.com
> CC: p.jimenez@ismsolar.com; pgsql-performance@postgresql.org
>
> 2012/11/6 Willem Leenen <willem_leenen@hotmail.com>:
> > @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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
