From: eleven@ludojad.itpp.pl
To: pgsql-performance@postgresql.org
Subject: Re: Seq scan vs. Index scan with different query conditions
Date: Mon, 5 Jul 2004 15:46:15 +0200
Message-ID: <20040705134615.GA18520@ludojad.itpp.pl> (raw)
In-Reply-To: <1089027854.6664.197.camel@lamb.mcmillan.net.nz>
References: <20040705101515.GA12879@ludojad.itpp.pl>
<1089027854.6664.197.camel@lamb.mcmillan.net.nz>
On Mon, Jul 05, 2004 at 11:44:13PM +1200, Andrew McMillan wrote:
> > DateTimeIndex was created on both columns (Date/Time):
> > CREATE INDEX "DateTimeIndex" ON "tablex" USING btree ("Date", "Time");
> PostgreSQL is always going to switch at some point, where the number of
> rows that have to be read from the table exceed some percentage of the
> total rows in the table.
> We can possibly be more helpful if you send EXPLAIN ANALYZE, rather than
> just EXPLAIN.
Unfortunately that seq scan vs. index scan
heuristic was wrong - full scan kills the machine
in no time due to large amount of INSERTs happening
in the background (I/O bottleneck).
> - Is this supposed to be a slice of midnight to 6pm, for each day
> between 28 June and 4 July? If you want a continuous period from
> Midnight 28 June -> 6pm 4 July you're better to have a single timestamp
> field.
> - It is unlikely that the , "Time" on your index is adding much to your
> selectivity, and it may be that you would be better off without it.
Yes, we've figured out that index on Date + Time is rather useless.
Thanks for the tip, we've created index upon Date column instead and
it should be enough.
> - the DISTINCT can screw up your results, and it usually means that the
> SQL is not really the best it could be. A _real_ need for DISTINCT is
> quite rare in my experience, and from what I have seen it adds overhead
> and tends to encourage bad query plans when used unnecessarily.
What do you mean? The reason for which there's DISTINCT in that query is
because I want to know how many unique rows is in the table.
Do you suggest selecting all rows and doing "DISTINCT"/counting
on the application level?
--
11.
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: eleven@ludojad.itpp.pl
Subject: Re: Seq scan vs. Index scan with different query conditions
In-Reply-To: <20040705134615.GA18520@ludojad.itpp.pl>
* 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