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.53rows=1186 width=60) ->  Hash  (cost=11.93 rows=210 width=40)       ->  Seq Scan on inventorysuppliers
(cost=11.93rows=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