Re: [SQL] JOIN index/sequential select problem

On Thu, 13 May 1999, Tom Lane wrote:
> I concur with the conclusion that this entry is bogus --- you haven't
> got a fully functional installation of gprof, evidently :-(. Assuming

Which is weird, since this is a clean install of redhat linux 6.0 :)

> How big did you say these tables were? The explain outputs you posted

The av_parts table has about 4 million rows, while inventorysuppliers only
has ~200 rows.

> before made them look pretty small ... but if you have not vacuumed the
> tables lately, the optimizer might have a bad idea of how big they are.

The table had been vacuumed about a week before, with no updates, inserts
or deletes in the mean time.

I ran vacuum again, and the query is done instantly, however the cost seem
a bit high, no?
Hash Join (cost=31373.53 rows=7218 width=100)
-> Index Scan using av_parts_rawpartnumber_index on av_parts (cost=31313.53 rows=1186 width=60)
-> Hash (cost=11.93 rows=210 width=40)
-> Seq Scan on inventorysuppliers (cost=11.93 rows=210 width=40)

> Several other people have reported s_lock_stuck() aborts recently;
> I don't think we quite know the cause yet...

Bummer, I haven't found a way to reproduce it yet.
I have had some other misc weird problems.
One of them was with vacuum. I was running 'vacuumdb -z -v procurement'
on one of my databases (~3GB) and it kept crashing the backend.
(gdb) bt
#0 0x810a28f in hash_search ()
#1 0x80d53e5 in BufTableLookup ()
#2 0x80d59c1 in BufferAlloc ()
#3 0x80d583d in ReadBufferWithBufferLock ()
#4 0x80d57d8 in ReadBuffer ()
#5 0x80886b2 in vc_scanheap ()
#6 0x808840c in vc_vacone ()
#7 0x8087dc1 in vc_vacuum ()
#8 0x8087cb3 in vacuum ()

Unfortunately I haven't compiled with -g..
The weird thing is that once I did a 'vacuumdb -v procurement', then I
could do a -z vacuum again and it worked...

Thanks,
Ole Gjerde