pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem on table statistics
4+ messages / 3 participants
[nested] [flat]
* problem on table statistics
@ 2012-01-09 14:41 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 2 replies; 4+ messages in thread
From: Silvio Brandani @ 2012-01-09 14:41 UTC (permalink / raw)
To: pgsql-admin
In the last few hours we get a problem with following query in
Production database :
select * from "001".mov_con where number in ( select number from
"001".mov_con where abs(amount-total_amo)>0.1) ;
The correct plan should be
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Nested Loop (cost=541763.01..584606.03 rows=1249640 width=360)
-> HashAggregate (cost=541763.01..541807.55 rows=4454 width=10)
-> Index Scan using mov_con_x9 on mov_con t2
(cost=0.00..538639.38 rows=1249452 width=10)
Filter: (abs((amount - total_amo)) > 0.1::double precision)
-> Index Scan using mov_con_pkey on mov_con t1 (cost=0.00..6.10
rows=281 width=360)
Index Cond: (t1.number = t2.number)
(6 rows)
instead we get the following WRONG one:
QUERY PLAN
-----------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.00..52906.16 rows=117499 width=620)
-> Index Scan using mov_con_x10 on mov_con t1 (cost=0.00..52483.90
rows=352486 width=620)
-> Index Scan using mov_con_x10 on mov_con t2 (cost=0.00..0.72
rows=3 width=11)
Index Cond: (t2.number = t1.number)
Filter: (abs((t2.amount - t2.total_amo)) > 0.1::double precision)
So I go to see statistics and try to change the
default_statistics_target from 10 to 100 , reload the configuration and
vacuum the table and the result is that while
the other tables have now 100 values on pg_stats the mov_con table
still have the same values
SELECT * FROM pg_stats
WHERE tablename='mov_con' AND attname='number' ;
So there is something wrong with table statistics. How can I reset the
pg_statistics for this table???
Any comment higly appreciated.
--
Silvio Brandani
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem on table statistics
@ 2012-01-09 14:59 Szymon Guz <mabewlun@gmail.com>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 1 reply; 4+ messages in thread
From: Szymon Guz @ 2012-01-09 14:59 UTC (permalink / raw)
To: Silvio Brandani <silvio.brandani@tech.sdb.it>; +Cc: pgsql-admin
On 9 January 2012 15:41, Silvio Brandani <silvio.brandani@tech.sdb.it>wrote:
>
> In the last few hours we get a problem with following query in Production
> database :
>
> select * from "001".mov_con where number in ( select number from
> "001".mov_con where abs(amount-total_amo)>0.1) ;
>
> The correct plan should be
>
> QUERY PLAN
> ------------------------------**------------------------------**
> ------------------------------**-----------
> Nested Loop (cost=541763.01..584606.03 rows=1249640 width=360)
> -> HashAggregate (cost=541763.01..541807.55 rows=4454 width=10)
> -> Index Scan using mov_con_x9 on mov_con t2
> (cost=0.00..538639.38 rows=1249452 width=10)
> Filter: (abs((amount - total_amo)) > 0.1::double precision)
> -> Index Scan using mov_con_pkey on mov_con t1 (cost=0.00..6.10
> rows=281 width=360)
> Index Cond: (t1.number = t2.number)
> (6 rows)
>
>
> instead we get the following WRONG one:
>
> QUERY PLAN
> ------------------------------**------------------------------**
> ------------------------------**-----
> Nested Loop IN Join (cost=0.00..52906.16 rows=117499 width=620)
> -> Index Scan using mov_con_x10 on mov_con t1 (cost=0.00..52483.90
> rows=352486 width=620)
> -> Index Scan using mov_con_x10 on mov_con t2 (cost=0.00..0.72 rows=3
> width=11)
> Index Cond: (t2.number = t1.number)
> Filter: (abs((t2.amount - t2.total_amo)) > 0.1::double precision)
>
> So I go to see statistics and try to change the default_statistics_target
> from 10 to 100 , reload the configuration and vacuum the table and the
> result is that while
> the other tables have now 100 values on pg_stats the mov_con table still
> have the same values
>
> SELECT * FROM pg_stats
> WHERE tablename='mov_con' AND attname='number' ;
>
> So there is something wrong with table statistics. How can I reset the
> pg_statistics for this table???
>
> Any comment higly appreciated.
>
>
>
>
>
>
Hi,
did you make only vacuum, or vacuum analyze? Simple vacuum does not change
stats, analyze does (or vacuum analyze).
regards
Szymon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem on table statistics
@ 2012-01-09 15:08 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Silvio Brandani @ 2012-01-09 15:08 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
There was a table set statistics changing the default value, now I
remove and run analyze the stats are update correctly but the problem of
different plan still stand.
I set enable_nestloop off and the query plan is the following, the query
is fast now:
Hash IN Join (cost=56574.39..118874.17 rows=117295 width=620)
Hash Cond: (t1.number = t2.number)
-> Index Scan using mov_con_x10 on mov_con t1 (cost=0.00..52469.07
rows=351884 width=620)
-> Hash (cost=55108.20..55108.20 rows=117295 width=11)
-> Index Scan using mov_con_x10 on mov_con t2
(cost=0.00..55108.20 rows=117295 width=11)
Filter: (abs((amount - total_amo)) > 0.1::double precision)
Il 09/01/2012 15.59, Szymon Guz ha scritto:
>
>
> On 9 January 2012 15:41, Silvio Brandani <silvio.brandani@tech.sdb.it
> <mailto:silvio.brandani@tech.sdb.it>> wrote:
>
>
> In the last few hours we get a problem with following query in
> Production database :
>
> select * from "001".mov_con where number in ( select number from
> "001".mov_con where abs(amount-total_amo)>0.1) ;
>
> The correct plan should be
>
> QUERY PLAN
> ------------------------------ ------------------------------
> ------------------------------ -----------
> Nested Loop (cost=541763.01..584606.03 rows=1249640
> <tel:1249640> width=360)
> -> HashAggregate (cost=541763.01..541807.55 rows=4454 width=10)
> -> Index Scan using mov_con_x9 on mov_con t2
> (cost=0.00..538639.38 rows=1249452 <tel:1249452> width=10)
> Filter: (abs((amount - total_amo)) > 0.1::double
> precision)
> -> Index Scan using mov_con_pkey on mov_con t1
> (cost=0.00..6.10 rows=281 width=360)
> Index Cond: (t1.number = t2.number)
> (6 rows)
>
>
> instead we get the following WRONG one:
>
> QUERY PLAN
> ------------------------------ ------------------------------
> ------------------------------ -----
> Nested Loop IN Join (cost=0.00..52906.16 rows=117499 width=620)
> -> Index Scan using mov_con_x10 on mov_con t1
> (cost=0.00..52483.90 rows=352486 width=620)
> -> Index Scan using mov_con_x10 on mov_con t2 (cost=0.00..0.72
> rows=3 width=11)
> Index Cond: (t2.number = t1.number)
> Filter: (abs((t2.amount - t2.total_amo)) > 0.1::double
> precision)
>
> So I go to see statistics and try to change the
> default_statistics_target from 10 to 100 , reload the
> configuration and vacuum the table and the result is that while
> the other tables have now 100 values on pg_stats the mov_con
> table still have the same values
>
> SELECT * FROM pg_stats
> WHERE tablename='mov_con' AND attname='number' ;
>
> So there is something wrong with table statistics. How can I reset
> the pg_statistics for this table???
>
> Any comment higly appreciated.
>
>
>
>
>
>
> Hi,
> did you make only vacuum, or vacuum analyze? Simple vacuum does not
> change stats, analyze does (or vacuum analyze).
>
>
> regards
> Szymon
--
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201119
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problem on table statistics
@ 2012-01-09 15:48 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2012-01-09 15:48 UTC (permalink / raw)
To: pgsql-admin; Silvio Brandani <silvio.brandani@tech.sdb.it>
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
> So there is something wrong with table statistics.
It picks the plan which it calculates to have the lowest cost. If
the lowest cost doesn't correspond to the fastest plan, the most
common cause is that your costing factors need adjustment.
> How can I reset the pg_statistics for this table???
It can also be that statistics are stale or are not fine-grained
enough. You can get fresh statistics with the ANALYZE or VACUUM
ANALYZE statements. You can change the granularity of statistics
globally or for particular columns, but that just changes the
behavior of subsequent ANALYZE runs; it doesn't force an immediate
run.
Your post was a little light on the sort of details which allow
people to be most helpful. If problems persist, please read this
before posting again:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-01-09 15:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-09 14:41 problem on table statistics Silvio Brandani <silvio.brandani@tech.sdb.it>
2012-01-09 14:59 ` Szymon Guz <mabewlun@gmail.com>
2012-01-09 15:08 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
2012-01-09 15:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox