From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ole Gjerde <gjerde(at)icebox(dot)org> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Index not used on simple select |
Date: | 1999-07-23 14:19:21 |
Message-ID: | 14469.932739561@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Ole Gjerde <gjerde(at)icebox(dot)org> writes:
> parts=> explain select * from av_parts where nsn = '123456';
> Seq Scan on av_parts (cost=194841.86 rows=3206927 width=124)
> [ why isn't it using the index on nsn? ]
That is darn peculiar. You're probably managing to trigger some nitty
little bug in the optimizer, but I haven't the foggiest what it might
be.
> Indices: av_parts_itemid_key
> av_parts_nsn_index
> av_parts_partnumber_index
One bit of info you didn't provide is how that third index is defined.
Shipping your 4-million-row database around is obviously out of the
question, but I think a reproducible test case is needed; it's going to
take burrowing into the code with a debugger to find this one. Can
you make a small test case that behaves the same way? (One thing
to try right away is loading the same table and index definitions into
an empty database, but *not* loading any data and not doing vacuum.
If that setup doesn't show the bug, try adding a couple thousand
representative rows from your real data, vacuum analyzing, and then
seeing if it happens.)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-07-23 14:48:45 | Re: [HACKERS] Phantom row from aggregate in self-join in 6.5 |
Previous Message | Lamar Owen | 1999-07-23 14:14:10 | Re: [HACKERS] Phantom row from aggregate in self-join in 6.5 |