[PERFORM] VACUUM ANALYZE downgrades performance
Hi all, On v7.4.5 I noticed downgrade in the planner, namely favoring sequential scan over index scan. The proof: create table a ( a integer); create index aidx on a(a); explain analyze select * from a where a = 0; -- Index Scan using aidx on a (cost=0.00..17.07 rows=5 width=4) (actual -- time=0.029..0.029 rows=0 loops=1) -- Index Cond: (a = 0) vacuum analyze; explain analyze select * from a where a = 0; -- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009 -- rows=0 loops=1) -- Filter: (a = 0) I do realize that there might be reasons why this happens over an empty table, but what is way worse that when the table starts actually to fill, the seq scan is still there, and the index is simply not used. How that could be so ...mmm... shortsighted, and what is more important, how to avoid this? I hope the answer is not 'run vacuum analyze each 5 seconds'. -- Sincerely, Dmitry Karasik --- catpipe Systems ApS *BSD solutions, consulting, development www.catpipe.net +45 7021 0050 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] VACUUM ANALYZE downgrades performance
On 11/30/2004 7:30 AM Dmitry Karasik said:: Hi all, On v7.4.5 I noticed downgrade in the planner, namely favoring sequential scan over index scan. The proof: create table a ( a integer); create index aidx on a(a); explain analyze select * from a where a = 0; -- Index Scan using aidx on a (cost=0.00..17.07 rows=5 width=4) (actual -- time=0.029..0.029 rows=0 loops=1) -- Index Cond: (a = 0) vacuum analyze; explain analyze select * from a where a = 0; -- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009 -- rows=0 loops=1) -- Filter: (a = 0) I do realize that there might be reasons why this happens over an empty table, but what is way worse that when the table starts actually to fill, the seq scan is still there, and the index is simply not used. How that could be so ...mmm... shortsighted, and what is more important, how to avoid this? I hope the answer is not 'run vacuum analyze each 5 seconds'. Look at the ACTUAL TIME. It dropped from 0.029ms (using the index scan) to 0.009ms (using a sequential scan.) Index scans are not always faster, and the planner/optimizer knows this. VACUUM ANALYZE is best run when a large proportion of data has been updated/loaded or in the off hours to refresh the statistics on large datasets. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] VACUUM ANALYZE downgrades performance
On 30 Nov 2004 14:30:37 +0100, Dmitry Karasik <[EMAIL PROTECTED]> wrote: > > Hi all, > > On v7.4.5 I noticed downgrade in the planner, namely favoring > sequential scan over index scan. The proof: > >create table a ( a integer); >create index aidx on a(a); >explain analyze select * from a where a = 0; >-- Index Scan using aidx on a (cost=0.00..17.07 rows=5 width=4) (actual >-- time=0.029..0.029 rows=0 loops=1) >-- Index Cond: (a = 0) >vacuum analyze; >explain analyze select * from a where a = 0; >-- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009 >-- rows=0 loops=1) >-- Filter: (a = 0) Looks to me like the seq scan is a better plan. The "actual time" went down. > > I do realize that there might be reasons why this happens over an empty > table, but what is way worse that when the table starts actually to fill, > the seq scan is still there, and the index is simply not used. How > that could be so ...mmm... shortsighted, and what is more important, > how to avoid this? I hope the answer is not 'run vacuum analyze each 5 > seconds'. > See this thread (http://archives.postgresql.org/pgsql-hackers/2004-11/msg00985.php and http://archives.postgresql.org/pgsql-hackers/2004-11/msg01080.php) for an ongoing discussion of the issue. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] "Group By " index usage
Did you test with ILIKE instead of lower LIKE lower ?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of sdfasdfas
sdfasdfs
Sent: mercredi 24 novembre 2004 18:37
To: [EMAIL PROTECTED]
Subject: [PERFORM] "Group By " index usage
I have a table with this index:
create index ARTISTS_NAME on ARTISTS (
lower(AR_NAME)
);
Te index is over a colum with this definition:
AR_NAME VARCHAR(256) null,
I want to optimize this query:
select * from artists where lower(ar_name) like
lower('a%') order by lower(ar_name) limit 20;
I think the planner should use the index i have. But the result of the
explain command is:
explain analyze select * from artists where
lower(ar_name) like lower('a%') order by
lower(ar_name) limit 20;
QUERY PLAN
-
Limit (cost=20420.09..20420.14 rows=20 width=360) (actual
time=2094.13..2094.19 rows=20 loops=1)
-> Sort (cost=20420.09..20433.52 rows=5374
width=360) (actual time=2094.13..2094.16 rows=21
loops=1)
Sort Key: lower((ar_name)::text)
-> Index Scan using artists_name on artists
(cost=0.00..19567.09 rows=5374 width=360) (actual
time=0.11..1391.97 rows=59047 loops=1)
Index Cond: ((lower((ar_name)::text) >=
'a'::text) AND (lower((ar_name)::text) < 'b'::text))
Filter: (lower((ar_name)::text) ~~
'a%'::text)
Total runtime: 2098.62 msec
(7 rows)
The "ORDER BY" clause is not using the index!. I don't know why.
I have the locale configured to C, and the index works well with the "like"
operator.
¿Could you help me? I am really lost.
__
Renovamos el Correo Yahoo!: ¡100 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Hardware purchase question
Bo, > 2 - 2.4 Ghz Xeon processors > 4GB ram > 4 36gb 1rpm scsi drives configured for raid 10 Hopefully you've turned OFF hyperthreading? > gains can I expect on average from swapping from 4 disk raid 10 to 14 disk > raid 10? Could I expect to see 40 - 50% better throughput. This is so dependant on application design that I can't possibly estimate. One big gain area for you will be moving the database log (pg_xlog) to its own private disk resource (such as a raid-1 pair). In high-write enviroments, this can gain you 15% without changing anything else. > The servers listed above are the dell 2650's which have perc 3 > controllers. I have seen on this list where they are know for not > performing well. So any suggestions for an attached scsi device would be > greatly appreciated. Also, any thoughts on fibre channel storage devices? The 2650s don't perform well in a whole assortment of ways. This is why they are cheap. NetApps seem to be the current best in NAS/SAN storage, although many people like EMC. Stay away from Apple's XRaid, which is not designed for databases. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] FW: Index usage
Hi, what do you mean by increasing the statistics on the date column? We never had any upgrade on it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gnari Sent: Thursday, November 25, 2004 3:13 AM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: Re: [PERFORM] FW: Index usage From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> > > Yes, the database is being vacuum-ed and analyzed on a daily basis. > then you should consider increating the statistics on the date column, as the estimates were a bit off in the plan > Our version is 7.2.1 upgrade time ? gnari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> > > Hi, what do you mean by increasing the statistics on the date column? alter table chatlogs alter column date set statistics 300; analyze chatlogs; > > > Our version is 7.2.1 > > > > upgrade time ? > > We never had any upgrade on it. 7.2 is a bit dated now that 8.0 is in beta if you want to stay with 7.2, you should at least upgrade to the latest point release (7.2.6 ?), as several serious bugs have been fixed gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] FW: Index usage
Thanks but whatever it does, it didn't work. :D Do you think upgrading will fix this problem? = db=# alter table chatlogs alter column date set statistics 300; ALTER db=# analyze chatlogs; ANALYZE db=# explain analyze select * from chatlogs where date >= '12/1/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61 rows=3357 width=212) (actual time=22.14..138.53 rows=1312 loops=1) Total runtime: 139.42 msec EXPLAIN morphTv=# explain analyze select * from chatlogs where date >= '11/03/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) (actual time=12.24..13419.36 rows=257137 loops=1) Total runtime: 13573.70 msec EXPLAIN = -Original Message- From: gnari [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 10:08 AM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: Re: [PERFORM] FW: Index usage From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> > > Hi, what do you mean by increasing the statistics on the date column? alter table chatlogs alter column date set statistics 300; analyze chatlogs; > > > Our version is 7.2.1 > > > > upgrade time ? > > We never had any upgrade on it. 7.2 is a bit dated now that 8.0 is in beta if you want to stay with 7.2, you should at least upgrade to the latest point release (7.2.6 ?), as several serious bugs have been fixed gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] FW: Index usage
If it's any help, i just ran this test on 7.4.6, my table has about 700 rows and the index is an integer. The item id ranges from 1 to 2. As you can see from the following plans, the optimizer changed it's plan depending on the value of the item id condition, and will use an index when it determines that the number of values that will be returned is a low % of the total table size. The item_id is an integer, but It looked like you are using a character field to store date information. Also, the dates you entered in your test case seem to be in the format DD/MM/YY which won't be amenable to useful comparative searching (I didn't read any of the earlier posts so if that isn't the case, just ignore this). If this is the case, try storing the data in a date column and see what happens then. regards Iain test=# explain analyse select * from bigtable where item_id <= 1000; QUERY PLAN --- Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57 rows=59553 width=80) (actual time=0.069..704.401 rows=58102 loops=1) Index Cond: ((item_id)::integer <= 1000) Total runtime: 740.786 ms (3 rows) test=# explain analyse select * from bigtable where item_id <= 1; QUERY PLAN --- --- Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589 width=80) (actual time=0.027..18599.032 rows=71 14844 loops=1) Filter: ((item_id)::integer <= 1) Total runtime: 23024.986 ms - Original Message - From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> To: "'gnari'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, December 01, 2004 11:33 AM Subject: Re: [PERFORM] FW: Index usage Thanks but whatever it does, it didn't work. :D Do you think upgrading will fix this problem? = db=# alter table chatlogs alter column date set statistics 300; ALTER db=# analyze chatlogs; ANALYZE db=# explain analyze select * from chatlogs where date >= '12/1/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61 rows=3357 width=212) (actual time=22.14..138.53 rows=1312 loops=1) Total runtime: 139.42 msec EXPLAIN morphTv=# explain analyze select * from chatlogs where date >= '11/03/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) (actual time=12.24..13419.36 rows=257137 loops=1) Total runtime: 13573.70 msec EXPLAIN = -Original Message- From: gnari [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 10:08 AM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: Re: [PERFORM] FW: Index usage From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> Hi, what do you mean by increasing the statistics on the date column? alter table chatlogs alter column date set statistics 300; analyze chatlogs; > > Our version is 7.2.1 > > upgrade time ? We never had any upgrade on it. 7.2 is a bit dated now that 8.0 is in beta if you want to stay with 7.2, you should at least upgrade to the latest point release (7.2.6 ?), as several serious bugs have been fixed gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] FW: Index usage
Hi. Thanks for your reply. The date column data type is date already. :D -Original Message- From: Iain [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 12:00 PM To: BBI Edwin Punzalan; 'gnari'; [EMAIL PROTECTED] Subject: Re: [PERFORM] FW: Index usage If it's any help, i just ran this test on 7.4.6, my table has about 700 rows and the index is an integer. The item id ranges from 1 to 2. As you can see from the following plans, the optimizer changed it's plan depending on the value of the item id condition, and will use an index when it determines that the number of values that will be returned is a low % of the total table size. The item_id is an integer, but It looked like you are using a character field to store date information. Also, the dates you entered in your test case seem to be in the format DD/MM/YY which won't be amenable to useful comparative searching (I didn't read any of the earlier posts so if that isn't the case, just ignore this). If this is the case, try storing the data in a date column and see what happens then. regards Iain test=# explain analyse select * from bigtable where item_id <= 1000; QUERY PLAN --- Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57 rows=59553 width=80) (actual time=0.069..704.401 rows=58102 loops=1) Index Cond: ((item_id)::integer <= 1000) Total runtime: 740.786 ms (3 rows) test=# explain analyse select * from bigtable where item_id <= 1; QUERY PLAN --- --- Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589 width=80) (actual time=0.027..18599.032 rows=71 14844 loops=1) Filter: ((item_id)::integer <= 1) Total runtime: 23024.986 ms - Original Message - From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> To: "'gnari'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, December 01, 2004 11:33 AM Subject: Re: [PERFORM] FW: Index usage > > Thanks but whatever it does, it didn't work. :D > > Do you think upgrading will fix this problem? > > = > db=# alter table chatlogs alter column date set statistics 300; ALTER > db=# analyze chatlogs; > ANALYZE > db=# explain analyze select * from chatlogs where date >= '12/1/04'; > NOTICE: QUERY PLAN: > > Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61 > rows=3357 width=212) (actual time=22.14..138.53 rows=1312 > loops=1) > Total runtime: 139.42 msec > > EXPLAIN > morphTv=# explain analyze select * from chatlogs where date >= > '11/03/04'; > NOTICE: QUERY PLAN: > > Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212) > (actual time=12.24..13419.36 rows=257137 loops=1) Total runtime: > 13573.70 msec > > EXPLAIN > = > > > > -Original Message- > From: gnari [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 01, 2004 10:08 AM > To: BBI Edwin Punzalan; [EMAIL PROTECTED] > Subject: Re: [PERFORM] FW: Index usage > > > From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]> > > >> >> Hi, what do you mean by increasing the statistics on the date column? > > alter table chatlogs alter column date set statistics 300; analyze > chatlogs; > >> > > Our version is 7.2.1 >> > >> > upgrade time ? >> >> We never had any upgrade on it. > > 7.2 is a bit dated now that 8.0 is in beta > > if you want to stay with 7.2, you should at least upgrade > to the latest point release (7.2.6 ?), as several serious bugs have > been fixed > > gnari > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Using "LIMIT" is much faster even though, searching with PK.
hello~ i'm curious about this situation. here is my test. my zipcode table has 47705 rows, and schema looks like this. pgsql=# \d zipcode Table "public.zipcode" Column | Type | Modifiers -+---+--- zipcode | character(7) | not null sido | character varying(4) | not null gugun | character varying(13) | not null dong | character varying(43) | not null bunji | character varying(17) | not null seq | integer | not null Indexes: "zipcode_pkey" PRIMARY KEY, btree (seq) and I need seq scan so, pgsql=# SET enable_indexscan TO OFF; SET Time: 0.534 ms now test start! the first row. pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '1'; QUERY PLAN --- Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=0.029..88.099 rows=1 loops=1) Filter: (seq = 1) Total runtime: 88.187 ms (3 rows) Time: 89.392 ms pgsql=# the first row with LIMIT pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '1' LIMIT 1; QUERY PLAN Limit (cost=0.00..1168.31 rows=1 width=207) (actual time=0.033..0.034 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=0.028..0.028 rows=1 loops=1) Filter: (seq = 1) Total runtime: 0.111 ms (4 rows) Time: 1.302 ms pgsql=# the last row, pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '47705'; QUERY PLAN --- Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=3.248..88.232 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 88.317 ms (3 rows) Time: 89.521 ms pgsql=# the last row with LIMIT, pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '47705' LIMIT 1; QUERY PLAN Limit (cost=0.00..1168.31 rows=1 width=207) (actual time=3.254..3.254 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=3.248..3.248 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 3.343 ms (4 rows) Time: 4.583 ms pgsql=# When I using index scan, the result was almost same, that means, there was no time difference, so i'll not mention about index scan. but, sequence scan, as you see above result, there is big time difference between using LIMIT and without using it. my question is, when we're searching with PK like SELECT * FROM table WHERE PK = 'xxx', we already know there is only 1 row or not. so, pgsql should stop searching when maching row was found, isn't it? i don't know exactly about mechanism how pgsql searching row its inside, so might be i'm thinking wrong way, any comments, advices, notes, anything will be appreciate to me! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] FW: Index usage
Sorry, i can't check this easily as I don't have any date fields in my data
(they all held has character strings - do as i say, not as i do) but maybe
you should cast or convert the string representation of the date to a date
in the where clause. Postgres might be doing some implicit conversion but if
it is, I'd expect it to use a -MM-DD format which is what I see here.
Something like ... WHERE date>= to_date('11/03/04','DD/MM/YY')
regards
Iain
- Original Message -
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>
To: "'Iain'" <[EMAIL PROTECTED]>; "'gnari'" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 1:05 PM
Subject: RE: [PERFORM] FW: Index usage
Hi. Thanks for your reply. The date column data type is date already. :D
-Original Message-
From: Iain [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 12:00 PM
To: BBI Edwin Punzalan; 'gnari'; [EMAIL PROTECTED]
Subject: Re: [PERFORM] FW: Index usage
If it's any help, i just ran this test on 7.4.6, my table has about
700
rows and the index is an integer.
The item id ranges from 1 to 2.
As you can see from the following plans, the optimizer changed it's plan
depending on the value of the item id condition, and will use an index
when
it determines that the number of values that will be returned is a low %
of
the total table size.
The item_id is an integer, but It looked like you are using a character
field to store date information. Also, the dates you entered in your test
case seem to be in the format DD/MM/YY which won't be amenable to useful
comparative searching (I didn't read any of the earlier posts so if that
isn't the case, just ignore this). If this is the case, try storing the
data
in a date column and see what happens then.
regards
Iain
test=# explain analyse select * from bigtable where item_id <= 1000;
QUERY
PLAN
---
Index Scan using d_bigtable_idx2 on bigtable (cost=0.00..118753.57
rows=59553 width=80) (actual
time=0.069..704.401 rows=58102 loops=1)
Index Cond: ((item_id)::integer <= 1000)
Total runtime: 740.786 ms
(3 rows)
test=# explain analyse select * from bigtable where item_id <= 1;
QUERY PLAN
---
---
Seq Scan on d_hi_mise_item_uri (cost=0.00..194285.15 rows=7140589
width=80) (actual time=0.027..18599.032 rows=71
14844 loops=1)
Filter: ((item_id)::integer <= 1)
Total runtime: 23024.986 ms
- Original Message -
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>
To: "'gnari'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 11:33 AM
Subject: Re: [PERFORM] FW: Index usage
Thanks but whatever it does, it didn't work. :D
Do you think upgrading will fix this problem?
=
db=# alter table chatlogs alter column date set statistics 300; ALTER
db=# analyze chatlogs;
ANALYZE
db=# explain analyze select * from chatlogs where date >= '12/1/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_type_idx on chatlogs (cost=0.00..6053.61
rows=3357 width=212) (actual time=22.14..138.53 rows=1312
loops=1)
Total runtime: 139.42 msec
EXPLAIN
morphTv=# explain analyze select * from chatlogs where date >=
'11/03/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..27252.86 rows=271882 width=212)
(actual time=12.24..13419.36 rows=257137 loops=1) Total runtime:
13573.70 msec
EXPLAIN
=
-Original Message-
From: gnari [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 10:08 AM
To: BBI Edwin Punzalan; [EMAIL PROTECTED]
Subject: Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>
Hi, what do you mean by increasing the statistics on the date column?
alter table chatlogs alter column date set statistics 300; analyze
chatlogs;
> > Our version is 7.2.1
>
> upgrade time ?
We never had any upgrade on it.
7.2 is a bit dated now that 8.0 is in beta
if you want to stay with 7.2, you should at least upgrade
to the latest point release (7.2.6 ?), as several serious bugs have
been fixed
gnari
---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Using "LIMIT" is much faster even though, searching with PK.
=?UTF-8?B?7J6l7ZiE7ISx?= <[EMAIL PROTECTED]> writes: > but, sequence scan, as you see above result, there is big time > difference between using LIMIT and without using it. You've got a table full of dead rows. Try VACUUM FULL ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Using "LIMIT" is much faster even though, searching
before test, I already executed VACUUM FULL. this result show up after vacuum full. Tom Lane ì ê: =?UTF-8?B?7J6l7ZiE7ISx?= <[EMAIL PROTECTED]> writes: but, sequence scan, as you see above result, there is big time difference between using LIMIT and without using it. You've got a table full of dead rows. Try VACUUM FULL ... regards, tom lane
Re: [PERFORM] Using "LIMIT" is much faster even though, searching
Hyun-Sang, > before test, I already executed VACUUM FULL. > this result show up after vacuum full. Really? Your results really look like a bloated table. Can you run VACUUM FULL ANALYZE VERBOSE on the table and post the output? > When I using index scan, the result was almost same, that means, there > was no time difference, so i'll not mention about index scan. Can we see an index scan plan anyway? EXPLAIN ANALYZE? Oh, and if this is a zip codes table, why are you using a sequence as the primary key instead of just using the zip code? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Using "LIMIT" is much faster even though, searching
do you need all of verbose information?? VACUUM FULL ANALYZE VERBOSE give me a lot of infomation, so i just cut zipcode parts. ==start=== INFO: vacuuming "public.zipcode" INFO: "zipcode": found 0 removable, 47705 nonremovable row versions in 572 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 76 to 136 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 27944 bytes. 0 pages are or will become empty, including 0 at the end of the table. 91 pages containing 8924 free bytes are potential move destinations. CPU 0.03s/0.00u sec elapsed 0.03 sec. INFO: index "zipcode_pkey" now contains 47705 row versions in 147 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.00 sec. INFO: "zipcode": moved 0 row versions, truncated 572 to 572 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.zipcode" INFO: "zipcode": scanned 572 of 572 pages, containing 47705 live rows and 0 dead rows; 3000 rows in sample, 47705 estimated total rows INFO: free space map: 108 relations, 128 pages stored; 1760 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 182 kB shared memory. VACUUM pgsql=# ==end=== USING INDEX SCAN ==start=== pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1';  QUERY PLAN --- ÂIndex Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.058 rows=1 loops=1)  Index Cond: (seq = 1) ÂTotal runtime: 0.152 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1; QUERY PLAN - ÂLimit (cost=0.00..3.02 rows=1 width=55) (actual time=0.059..0.060 rows=1 loops=1)  -> Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.054 rows=1 loops=1) Index Cond: (seq = 1) ÂTotal runtime: 0.158 ms (4 rows) pgsql=# WHEN SELECT LAST ROW - pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705';  QUERY PLAN --- ÂIndex Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.059 rows=1 loops=1)  Index Cond: (seq = 47705) ÂTotal runtime: 0.150 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705' LIMIT 1; QUERY PLAN - ÂLimit (cost=0.00..3.02 rows=1 width=55) (actual time=0.057..0.057 rows=1 loops=1)  -> Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.052..0.052 rows=1 loops=1) Index Cond: (seq = 47705) ÂTotal runtime: 0.156 ms (4 rows) pgsql=# ==end=== USING SEQUENCE SCAN ==start=== pgsql=# set enable_indexscan to off; SET pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1';  QUERY PLAN --- ÂSeq Scan on zipcode (cost=0.00..1168.31 rows=1 width=55) (actual time=0.032..109.934 rows=1 loops=1)  Filter: (seq = 1) ÂTotal runtime: 110.021 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1;  QUERY PLAN --- ÂLimit (cost=0.00..1168.31 rows=1 width=55) (actual time=0.035..0.035 rows=1 loops=1)  -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=55) (actual time=0.030..0.030 rows=1 loops=1) Filter: (seq = 1) ÂTotal runtime: 0.113 ms (4 rows) pgsql=# WHEN SELECT LAST ROW - pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705';  QUERY PLAN --- ÂSeq Scan on zipcode (cost=0.00..116
