From: Richard Huxton <dev@archonet.com>
To: BBI Edwin Punzalan <edwin@bluebamboo.ph>
Cc: pgsql-performance@postgresql.org
Subject: Re: FW: FW: Index usage
Date: Wed, 24 Nov 2004 10:16:53 +0000
Message-ID: <41A45F95.70206@archonet.com> (raw)
In-Reply-To: <001d01c4d1fc$ab997060$b400a8c0@bluebamboo.ph>
References: <001d01c4d1fc$ab997060$b400a8c0@bluebamboo.ph>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, edwin@bluebamboo.ph
Subject: Re: FW: FW: Index usage
In-Reply-To: <41A45F95.70206@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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