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