Обсуждение: problem with pg_statistics
Hi,
i think i need a little help with a problem with pg_statistic.
Lets say i have a table to collect traffic-data.
The table has a column time_stamp of type timesamptz.
The table has a single-column index on time_stamp.
The table has around 5 million records.
If i delete all statistical data from pg_statistic and do a
explain analyze i got this result.
-------------------------------------------------------------------------
explain analyze select * from tbl_traffic where tbl_traffic.time_stamp >= '2003-05-01' and tbl_traffic.time_stamp <
'2003-06-01';
NOTICE: QUERY PLAN:
Index Scan using idx_ts on tbl_traffic (cost=0.00..97005.57 rows=24586 width=72) (actual time=0.19..7532.63
rows=1231474loops=1)
Total runtime: 8179.08 msec
EXPLAIN
-------------------------------------------------------------------------
after i do a vacuum full verbose analyze i got the following result.
-------------------------------------------------------------------------
explain analyze select * from tbl_traffic where tbl_traffic.time_stamp >= '2003-05-01' and tbl_traffic.time_stamp <
'2003-06-01';
NOTICE: QUERY PLAN:
Seq Scan on tbl_traffic (cost=0.00..127224.24 rows=1197331 width=52) (actual time=0.03..14934.70 rows=1231474 loops=1)
Total runtime: 15548.35 msec
EXPLAIN
-------------------------------------------------------------------------
now i disable seqscans with set enable_seqscan to off
and i got the following.
-------------------------------------------------------------------------
explain analyze select * from tbl_traffic where tbl_traffic.time_stamp >= '2003-05-01' and tbl_traffic.time_stamp <
'2003-06-01';
NOTICE: QUERY PLAN:
Index Scan using idx_ts on tbl_traffic (cost=0.00..3340294.11 rows=1197331 width=52) (actual time=0.21..7646.29
rows=1231474loops=1)
Total runtime: 8285.92 msec
EXPLAIN
-------------------------------------------------------------------------
Could anybody explain or give some hint why the index is not used
although it is faster than a sequence-scan ?
BTW:
version
-----------------------------------------------------------
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
Thanks in advance, as
Andre Schubert <andre@km3.de> writes:
> i think i need a little help with a problem with pg_statistic.
Try reducing random_page_cost --- although you'd be foolish to set it on
the basis of just a single test query. Experiment with a few different
tables, and keep in mind that repeated tests will be affected by caching.
regards, tom lane
On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Andre Schubert <andre@km3.de> writes:
>> i think i need a little help with a problem with pg_statistic.
>
>Try reducing random_page_cost
With index scan cost being more than 25 * seq scan cost, I guess that
- all other things held equal - even random_page_cost = 1 wouldn't
help.
Andre might also want to experiment with effective_cache_size and with
ALTER TABLE ... SET STATISTICS.
Or there's something wrong with correlation?
Andre, what hardware is this running on? What are the values of
shared_buffers, random_page_cost, effective_cache_size, ... ? Could
you show us the result of
SELECT * FROM pg_stats
WHERE tablename = "tbl_traffic" AND attname = "time_stamp";
Servus
Manfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
>> Try reducing random_page_cost
> With index scan cost being more than 25 * seq scan cost, I guess that
> - all other things held equal - even random_page_cost = 1 wouldn't
> help.
Oh, you're right, I was comparing the wrong estimated costs. Yeah,
changing random_page_cost won't fix it.
> Or there's something wrong with correlation?
That seems like a good bet. Andre, is this table likely to be
physically ordered by time_stamp, or nearly so? If so, do you
expect that condition to persist, or is it just an artifact of
a test setup?
regards, tom lane
On Thu, 26 Jun 2003 12:03:52 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Manfred Koizar <mkoi-pg@aon.at> writes: > > On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl@sss.pgh.pa.us> > > wrote: > >> Try reducing random_page_cost > > > With index scan cost being more than 25 * seq scan cost, I guess that > > - all other things held equal - even random_page_cost = 1 wouldn't > > help. > > Oh, you're right, I was comparing the wrong estimated costs. Yeah, > changing random_page_cost won't fix it. > > > Or there's something wrong with correlation? > > That seems like a good bet. Andre, is this table likely to be > physically ordered by time_stamp, or nearly so? If so, do you > expect that condition to persist, or is it just an artifact of > a test setup? > First of all thanks for the quick response. We have three servers at different places, all servers are running with athlon processors and have ram between 512M up to 1024M, and a frequency between 700 and 1400Mhz. All servers running under Linux 7.2 Kernel 2.4.20. We use this table to collect traffic of our clients. Traffic data are inserted every 5 minutes with the actual datetime of the transaction, thatswhy the table should be physically order by time_stamp. All servers are running in production and i could reproduce the problem on all three servers. To answer Manfreds questions: > Andre, what hardware is this running on? What are the values of > shared_buffers, random_page_cost, effective_cache_size, ... ? Could > you show us the result of > > SELECT * FROM pg_stats > WHERE tablename = "tbl_traffic" AND attname = "time_stamp"; The only changes we have made are sort_mem = 32000 shared_buffers = 13000 All other values are commented out and should be set to default by postgres itself. #max_fsm_relations = 100 # min 10, fsm is free space map #max_fsm_pages = 10000 # min 1000, fsm is free space map #effective_cache_size = 1000 # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 Hope this help ... Thanks, as
On Thu, 26 Jun 2003 12:03:52 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Manfred Koizar <mkoi-pg@aon.at> writes:
> > On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> > wrote:
> >> Try reducing random_page_cost
>
> > With index scan cost being more than 25 * seq scan cost, I guess that
> > - all other things held equal - even random_page_cost = 1 wouldn't
> > help.
>
> Oh, you're right, I was comparing the wrong estimated costs. Yeah,
> changing random_page_cost won't fix it.
>
> > Or there's something wrong with correlation?
>
> That seems like a good bet. Andre, is this table likely to be
> physically ordered by time_stamp, or nearly so? If so, do you
> expect that condition to persist, or is it just an artifact of
> a test setup?
>
Sorry forgot the pg_stat query...
SELECT * FROM pg_stats where tablename = 'tbl_traffic' and attname = 'time_stamp';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs
|
histogram_bounds
| correlation
-------------+------------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------+--------------------------------------------------------------------+---------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------+-------------
tbl_traffic | time_stamp | 0 | 8 | 104009 | {"2003-06-03 19:12:01.059625+02","2003-02-03
19:52:06.666296+01","2003-02-1309:59:45.415763+01","2003
-02-28 18:10:28.536399+01","2003-04-11 18:09:42.30363+02","2003-04-26 20:35:50.110235+02","2003-05-03
11:09:32.991507+02","2003-05-2009:53:51.271853+02","2003-05-21 2
0:55:59.155387+02","2003-06-02 02:38:28.823182+02"} |
{0.00133333,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001}| {"2002-07-01 00:00:00+02","2003-02-21 01:59:
46.107696+01","2003-03-11 15:00:37.418521+01","2003-03-26 18:14:50.028972+01","2003-04-10
13:43:20.75909+02","2003-04-2709:03:19.592213+02","2003-05-08 22:35:41.99761
6+02","2003-05-22 15:34:42.932958+02","2003-06-03 00:53:05.870782+02","2003-06-15 08:45:41.154875+02","2003-06-27
07:18:30.265868+02"}| -0.479749
(1 row)
Thanks, as
On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert
<andre.schubert@km3.de> wrote:
>Traffic data are inserted every 5 minutes with the actual datetime
>of the transaction, thatswhy the table should be physically order by time_stamp.
So I'd expect a correlation of nearly 1. Why do your statistics show
a value of -0.479749? A negative correlation is a sign of descending
sort order, and correlation values closer to 0 indicate poor
correspondence between column values and tuple positions.
Could this be the effect of initial data loading? Are there any
updates or deletions in your traffic table?
>To answer Manfreds questions:
>> Andre, what hardware is this running on? What are the values of
>> shared_buffers, random_page_cost, effective_cache_size, ... ? Could
>> you show us the result of
>>
>> SELECT * FROM pg_stats
>> WHERE tablename = "tbl_traffic" AND attname = "time_stamp";
^ ^ ^ ^
Oops, these should have been single quotes. It's too hot here these
days :-)
>sort_mem = 32000
>shared_buffers = 13000
Personally I would set them to lower values, but if you have good
reasons ...
>#effective_cache_size = 1000 # default in 8k pages
This is definitely too low. With 512MB or more I tend to set this to
ca. 80% of available RAM. Use top and free to find hints for good
values.
Servus
Manfred
On Fri, 27 Jun 2003 10:43:01 +0200 Manfred Koizar <mkoi-pg@aon.at> wrote: > On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert > <andre.schubert@km3.de> wrote: > >Traffic data are inserted every 5 minutes with the actual datetime > >of the transaction, thatswhy the table should be physically order by time_stamp. > > So I'd expect a correlation of nearly 1. Why do your statistics show > a value of -0.479749? A negative correlation is a sign of descending > sort order, and correlation values closer to 0 indicate poor > correspondence between column values and tuple positions. > > Could this be the effect of initial data loading? Are there any > updates or deletions in your traffic table? > We dont make updates the traffic table. Once a month we delete the all data of the oldest month. And after that a vacuum full verbose analyze is performed. Could this cause reordering of the data ? And should i do a cluster idx_ts tbl_traffic ? > >To answer Manfreds questions: > >> Andre, what hardware is this running on? What are the values of > >> shared_buffers, random_page_cost, effective_cache_size, ... ? Could > >> you show us the result of > >> > >> SELECT * FROM pg_stats > >> WHERE tablename = "tbl_traffic" AND attname = "time_stamp"; > ^ ^ ^ ^ > Oops, these should have been single quotes. It's too hot here these > days :-) > You are so right ... :) > >#effective_cache_size = 1000 # default in 8k pages > > This is definitely too low. With 512MB or more I tend to set this to > ca. 80% of available RAM. Use top and free to find hints for good > values. > Ok, i will talk with my coworker ( he is the sysadmin of our machine ) and look if can use such amount of RAM, because there are several other processes that are running on these machines. But i will test and report ... Thanks, as
On Fri, 27 Jun 2003 11:10:58 +0200, Andre Schubert <andre@km3.de> wrote: >Once a month we delete the all data of the oldest month. >And after that a vacuum full verbose analyze is performed. >Could this cause reordering of the data ? I may be wrong, but I think VACUUM FULL starts taking tuples from the end of the relation and puts them into pages at the beginning until read and write position meet somewhere in the middle. This explains the bad correlation. >And should i do a cluster idx_ts tbl_traffic ? I think so. >> >#effective_cache_size = 1000 # default in 8k pages >> >> This is definitely too low. With 512MB or more I tend to set this to >> ca. 80% of available RAM. Use top and free to find hints for good >> values. >> > >Ok, i will talk with my coworker ( he is the sysadmin of our machine ) >and look if can use such amount of RAM, because there are several other >processes that are running on these machines. >But i will test and report ... effective_cache_size does not *control* resource consumption, it just *reports* it as a hint to the planner. Servus Manfred