[PERFORM] FW: Index usage
Hi everyone, Can anyone please explain postgres' behavior on our index. I did the following query tests on our database: db=# create index chatlogs_date_idx on chatlogs (date); CREATE db=# explain select date from chatlogs where date='11/23/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36 width=4) EXPLAIN db=# explain select date from chatlogs where date='10/23/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4) EXPLAIN Date's datatype is date. Its just odd that I just change the actual date of search and the index is not being used anymore. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
FW: [PERFORM] FW: Index usage
Thanks, Tim. I tried adding an upper limit and its still the same as follows: == db=# explain analyze select date from chatlogs where date='11/24/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37 width=4) (actual time=0.18..239.69 rows=10737 loops=1) Total runtime: 246.22 msec EXPLAIN db=# explain analyze select date from chatlogs where date='11/23/04' and date'11/24/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec EXPLAIN db=# explain analyze select date from chatlogs where date='11/23/04' and date'11/25/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.45..4268.00 rows=23787 loops=1) Total runtime: 4282.81 msec == How come a query on the current date filter uses an index and the others does not? This makes indexing to speed up queries quite difficult. -Original Message- From: Leeuw van der, Tim [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 3:35 PM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: RE: [PERFORM] FW: Index usage Well you just selected a whole lot more rows... What's the total number of rows in the table? In general, what I remember from reading on the list, is that when there's no upper bound on a query like this, the planner is more likely to choose a seq. scan than an index scan. Try to give your query an upper bound like: select date from chatlogs where date='11/23/04' and date '12/31/99'; select date from chatlogs where date='10/23/04' and date '12/31/99'; This should make it easier for the planner to give a proper estimate of the number of rows returned. If it doesn't help yet, please post 'explain analyze' output rather than 'explain' output, for it allows much better investigation into why the planner chooses what it chooses. cheers, --Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of BBI Edwin Punzalan Sent: Wednesday, November 24, 2004 7:52 AM To: [EMAIL PROTECTED] Subject: [PERFORM] FW: Index usage Hi everyone, Can anyone please explain postgres' behavior on our index. I did the following query tests on our database: db=# create index chatlogs_date_idx on chatlogs (date); CREATE db=# explain select date from chatlogs where date='11/23/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36 width=4) EXPLAIN db=# explain select date from chatlogs where date='10/23/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4) EXPLAIN Date's datatype is date. Its just odd that I just change the actual date of search and the index is not being used anymore. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] FW: Index usage
Yes, the database is being vacuum-ed and analyzed on a daily basis. Our version is 7.2.1 -Original Message- From: gnari [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 4:35 PM To: BBI Edwin Punzalan; [EMAIL PROTECTED] Subject: Re: [PERFORM] FW: Index usage From: BBI Edwin Punzalan [EMAIL PROTECTED] db=# explain analyze select date from chatlogs where date='11/23/04' and date'11/25/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.45..4268.00 rows=23787 loops=1) Total runtime: 4282.81 msec == How come a query on the current date filter uses an index and the others does not? This makes indexing to speed up queries quite difficult. have you ANALYZED the table lately ? what version postgres are you using ? gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: FW: [PERFORM] FW: Index usage
Hi. 1) chatlogs rows increases every now and then (its in a live environment) and currently have 538,696 rows 2) this is the only problem we experienced. So far, all our other indexes are being used correctly. 3) I don't remember tuning any post-installation configuration of our postgreSQL except setting fsync to false. Thanks for taking a look at our problem. :D -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 6:17 PM To: BBI Edwin Punzalan Cc: [EMAIL PROTECTED] Subject: Re: FW: [PERFORM] FW: Index usage BBI Edwin Punzalan wrote: Thanks, Tim. I tried adding an upper limit and its still the same as follows: == db=# explain analyze select date from chatlogs where date='11/24/04'; NOTICE: QUERY PLAN: Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..145.72 rows=37 width=4) (actual time=0.18..239.69 rows=10737 loops=1) Total runtime: 246.22 msec EXPLAIN db=# explain analyze select date from chatlogs where date='11/23/04' and date'11/24/04'; NOTICE: QUERY PLAN: Seq Scan on chatlogs (cost=0.00..24763.19 rows=9200 width=4) (actual time=0.44..4447.01 rows=13029 loops=1) Total runtime: 4455.56 msec We have two issues here 1. In the first example it only picks an index because it thinks it is going to get 37 rows, it actually gets 10737 2. It's taking 4455ms to run a seq-scan but only 246ms to run an index-scan over 10737 rows (and then fetch the rows too). Questions: 1. How many rows do you have in chatlogs? 2. Is this the only problem you are experiencing, or just one from many? 3. Have you tuned any configuration settings? e.g. as suggested in: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Richard Huxton Archonet Ltd ---(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] 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
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
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
