"Mayers, Philip J" <p.mayers@ic.ac.uk> writes:
> 1) The costs seem to change radically without me taking any action - do I
> have to run them on an unloaded machine?
> 2) VACUUM seemed to massively increase the cost
> 3) VACUUM ANALYZE decreased it - so, should I always do "VACUUM; VACUUM
> ANALYZE;"
The cost estimates certainly don't depend on load ;-). They aren't
going to change when you "haven't taken any action", either. However,
they do depend on statistics gathered by VACUUM and VACUUM ANALYZE,
so the estimates can change after you run those.
> 4) I'm using LIMIT after the outer join - should I LIMIT the two inner joins
> to the same amount, since or will Postgres do this itself?
Hard to say. You're not going to get much useful response about a query
as complex as this one evidently is when you haven't shown us the query
itself and the declarations of the relevant tables/indexes.
> a join of the form "host.mac = interface.mac", so I should hash index the
> host.mac column? Unfortunately, mac is of type "macaddr" and this happens:
> hdb=> create index host_mac on host using hash (mac);
> ERROR: fmgr_info: function 0: cache lookup failed
Hash indexes on macaddr don't work in 7.0 (the support's only partially
present). Personally I can see very little use for hash indexes anyway.
I don't know of any situation where I'd prefer hash to btree, at least
not given the implementations of the two in Postgres.
regards, tom lane