[postgis-users] [GENERAL] How to improve this query?

[Stefan Keller]

Hi

>> I thought hash indexes were discouraged over btree. Anyway, I'll test it.
>
> Absolutely. You do not want to be using Hash Indexes these days - stick with
> B-Tree. The hash refers to the method by which the executor matches the two
> column fields and is not related to the underlying index type.

Yeah, I know that btrees are the preferred way - except for the match
case "=". That's at least what docu. implicitly says:
http://www.postgresql.org/docs/9.0/static/indexes-types.html

So the note there is incomplete?
> Caution: Hash index operations are not presently WAL-logged, so hash
> indexes might need to be rebuilt with REINDEX after a database crash.
> They are also not replicated over streaming or file-based replication.
> For these reasons, hash index use is presently discouraged.
... and should also note, that hashes even don't outperform btrees for
= operator?

Yours, S.


2011/2/8 Mark Cave-Ayland <mark.cave-ayland at siriusit.co.uk>:
> On 08/02/11 11:05, Jorge Arévalo wrote:
>
>> 2011/2/8 Stefan Keller<sfkeller at gmail.com>:
>>>
>>> "  Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)"
>>> tells me that these two longish strings are compared first.
>>> Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD
>>> as hash instead btree?
>>
>> I thought hash indexes were discouraged over btree. Anyway, I'll test it.
>
> Absolutely. You do not want to be using Hash Indexes these days - stick with
> B-Tree. The hash refers to the method by which the executor matches the two
> column fields and is not related to the underlying index type.
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>