Skip site navigation (1) Skip section navigation (2)

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

From: eleven(at)ludojad(dot)itpp(dot)pl
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Seq scan vs. Index scan with different query conditions
Date: 2004-07-05 13:46:15
Message-ID: 20040705134615.GA18520@ludojad.itpp.pl (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Andrew McMillanDate: 2004-07-05 19:22:05
Subject: Re: Seq scan vs. Index scan with different query
Previous:From: Andrew McMillanDate: 2004-07-05 11:44:13
Subject: Re: Seq scan vs. Index scan with different query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group