pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Roman Fail <rfail@posportal.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.1 New install, large queries are slow
Date: Thu, 16 Jan 2003 11:35:55 -0800 (PST)
Message-ID: <20030116112424.S7433-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C06@pos_pdc.posportal.com>


On Thu, 16 Jan 2003, Roman Fail wrote:

> > Stephan Szabo wrote:
> > I'd assume that tranamount values are fairly randomly distributed
> > throughout the table, right?  It takes about 5 minutes for the
> > system to read the entire table and more for the index scan, so
> > you're probably reading most of the table randomly and the index
> > as well.
> > What values on batchdetail do you use in query where clauses regularly?

>  Yes, tranamount values are randomly distributed.  I don't understand
> why an index scan would be "random", isn't the whole point of an index
> to have an ordered reference into the data?  batchdetail has 5 columns
> that can be in the WHERE clause, all of which are indexed.  None is
> more likely than the other to be searched, so a clustered index
> doesn't make much sense to me.  The whole thing needs to be fast.

Yeah, in that case a clustered index doesn't help.
Indexes give you an ordered way to find the rows that meet a condition,
but say you had three rows in your table in this order (note that this is
an amazing oversimplification):
(1,'a')
(2,'b')
(0,'c')

And you want to scan the index from values with the first number between 0
and 2.  It reads the third row, then the first, then the second (to get
the letter associated).  Between those reads, it's got to seek back and
forth through the heap file and the order in which it hits them is pretty
random seeming (to the kernel).

> > Ron Johnson wrote:
> > What are the indexes on batchdetail?
> > There's one on batchid and a seperate one on tranamount?
> > If so, what about dropping them and create a single multi-segment
> > index on "batchid, tranamount".  (A constraint can then enforce
> > uniqueness on batchid.
>  There is no index on batchid, I think it is a good idea to create
> one.  Stephan also suggested this.  After I try the single batchid
> index, I might try to multi-segment index idea as well.  I'll post
> results later today.

I think we may all have misread the index list to include an index on
batchid. Also you have two indexes on batchdetailid right now (primary key
also creates one) which added to the confusion.

> > Stephan Szabo wrote:
> > Then I realized that batchheader.batchid and
> > batchdetail.batchid don't even have the same
> > type, and that's probably something else you'd
> > need to fix.
>
> Yes, that's a mistake on my part....batchdetail(batchid) should be an
> int8.  It looks to me like converting this datatype can't be done with
> a single ALTER TABLE ALTER COLUMN statement.....so I guess I'll work
> around it with an ADD, UPDATE, DROP, and RENAME.

Don't forget to do a vacuum full in there as well.






view thread (66+ messages)  latest in thread

Message-ID: <20030116112424.S7433-100000@megazone23.bigpanda.com>
Permalink:  ../20030116112424.S7433-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20030116112424.S7433-100000@megazone23.bigpanda.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, rfail@posportal.com, josh@agliodbs.com, tgl@sss.pgh.pa.us
  Subject: Re: 7.3.1 New install, large queries are slow
  In-Reply-To: <20030116112424.S7433-100000@megazone23.bigpanda.com>
  References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C06@pos_pdc.posportal.com> <20030116112424.S7433-100000@megazone23.bigpanda.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