Re: help with too slow query
| От | Willem Leenen |
|---|---|
| Тема | Re: help with too slow query |
| Дата | |
| Msg-id | DUB104-W5416A60BE516D5A92A16E08F6B0@phx.gbl обсуждение исходный текст |
| Ответ на | Re: help with too slow query (Виктор Егоров <vyegorov@gmail.com>) |
| Ответы |
Re: help with too slow query
|
| Список | pgsql-performance |
@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
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: [PERFORM] help with too slow query
> From: vyegorov@gmail.com
> To: p.jimenez@ismsolar.com
> CC: pgsql-performance@postgresql.org
>
> 2012/11/6 Pedro Jiménez Pérez <p.jimenez@ismsolar.com>
> > 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> Subject: Re: [PERFORM] help with too slow query
> From: vyegorov@gmail.com
> To: p.jimenez@ismsolar.com
> CC: pgsql-performance@postgresql.org
>
> 2012/11/6 Pedro Jiménez Pérez <p.jimenez@ismsolar.com>
> > 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
В списке pgsql-performance по дате отправления: