Re: FW: FW: Index usage

Поиск
Список
Период
Сортировка
От BBI Edwin Punzalan
Тема Re: FW: FW: Index usage
Дата
Msg-id 000701c4d211$f039bfd0$b400a8c0@bluebamboo.ph
обсуждение исходный текст
Ответ на Re: FW: FW: Index usage  (Richard Huxton <dev@archonet.com>)
Ответы Re: FW: FW: Index usage  (Richard Huxton <dev@archonet.com>)
Список pgsql-performance
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:dev@archonet.com]
Sent: Wednesday, November 24, 2004 6:17 PM
To: BBI Edwin Punzalan
Cc: pgsql-performance@postgresql.org
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: FW: FW: Index usage
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: FW: FW: Index usage