Re: [HACKERS] Heh, the disappearing problem!

On Mon, Mar 09, 1998 at 04:58:59PM -0500, Bruce Momjian wrote:
> >
> >
> > Hi folks,
> >
> > Remember me?  Remember I was bitching about hashed and indexes scans not
> > being indexed?
> >
> > Guess what - it magically fixed itself.
> >
> > If you want to talk about things that *bother* me, this one tops the pack.
> >
> > The same query now returns an index hash query plan, which executes in a few
> > seconds and requires little memory (as opposed to looped sequential scans
> > requiring 500MB on the server).
> >
> > This is really, really, odd.
>
> Vacuum?  Vacuum analyze?  Improper index?

Nope, nope and it was working until this morning with no indicated errors.

This and the text indices are the two really, really odd things I'm seeing
in 6.3, along with *some* operations taking a lot longer than they used to.

One of the things we do around here from libpq is an operation that looks
like this:


1)    Select a set of records from a join on a couple of tables.

2)    Select from a different table (using the results from the first
    select), looking specifically for certain data which we then use
    programmatically to perform a set of computations.

Now, the first select is just peachy.  It returns ~1500 or so records.

The iterative loop on the second select used to run through the entire 1500
records or so (doing a select for each) in ~20-30 seconds.  Now it takes
roughly 2-3 minutes to do the same job.  I have checked with "explain" that
the indices are being used for all queries - they are.

I've seen this also with a few other processes that do the same kind of
thing, and get the same results.

I'm wondering if what we're seeing here is a severe degredation of locking
performance.  That is, could this be related to the new deadlock detection
code?  We're doing selects/updates within several tables  in these jobs, but
all within one transaction block (begin/commit or begin/rollback).

--
--
Karl Denninger (karl@MCS.Net)| MCSNet - Serving Chicagoland and Wisconsin
http://www.mcs.net/          | T1's from $600 monthly to FULL DS-3 Service
                 | NEW! K56Flex support on ALL modems
Voice: [+1 312 803-MCS1 x219]| EXCLUSIVE NEW FEATURE ON ALL PERSONAL ACCOUNTS
Fax:   [+1 312 803-4929]     | *SPAMBLOCK* Technology now included at no cost