pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (5+ messages)  latest in thread

Message-ID: <20040705134615.GA18520@ludojad.itpp.pl>
Permalink:  ../20040705134615.GA18520@ludojad.itpp.pl/
Also on:    postgresql.org/message-id/20040705134615.GA18520@ludojad.itpp.pl

 · 

reply

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