Re: Seq scan vs. Index scan with different query conditions

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.