Re: help with too slow query - Mailing list pgsql-performance

From Willem Leenen
Subject Re: help with too slow query
Date
Msg-id DUB104-W15E74595B06FB1B6281F08F6B0@phx.gbl
Whole thread Raw
In response to Re: help with too slow query  (Виктор Егоров <vyegorov@gmail.com>)
List pgsql-performance
@Victor,
 
Spasibo for the information, seems valid to me. 
 
Regards,
Willem Leenen

 
> 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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] pg_dump and thousands of schemas
Next
From: Denis
Date:
Subject: Re: [HACKERS] pg_dump and thousands of schemas