Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL

От: Tom Lane
Тема: Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
Дата: ,
Msg-id: 3485.1115819827@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: "Hash index" vs. "b-tree index" (PostgreSQL 8.0)  (Ying Lu)
Ответы: Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway)
Список: pgsql-performance

Скрыть дерево обсуждения

"Hash index" vs. "b-tree index" (PostgreSQL 8.0)  (Ying Lu, )
 Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL 8.0)  (Neil Conway, )
  Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL 8.0)  (Christopher Petrilli, )
   Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
    Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  ("Jim C. Nasby", )
     Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
      Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  ("Jim C. Nasby", )
       Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
        Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
         Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
          Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
           Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
           Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark, )
            Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
             Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark, )
              Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
               Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark, )
                Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
                 Federated PG servers -- Was: Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Mischa Sandberg, )
         Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  ("Jim C. Nasby", )
          Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
           Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  ("Jim C. Nasby", )
            Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Mischa Sandberg, )
        Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  ("Jim C. Nasby", )
         Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
         Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Mischa Sandberg, )
          Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Mark Lewis, )
          Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
           Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Mischa Sandberg, )
       Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Bruce Momjian, )
 Re: "Hash index" vs. "b-tree index" (PostgreSQL 8.0)  (David Roussel, )
 Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Tom Lane, )
  Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Neil Conway, )
 Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark, )

Neil Conway <> writes:
> Greg Stark wrote:
>> What if the hash index stored *only* the hash code?

> Attached is a WIP patch that implements this.

Performance?

> I'm posting mainly because I wasn't sure what to do to avoid false
> positives in the case of hash collisions. In the hash AM code it is
> somewhat awkward to fetch the pointed-to heap tuple and recheck the
> scankey.[1] I just did the first thing that came to mind -- I marked all
> the hash AM opclasses as "lossy", so the index qual is rechecked. This
> works, but suggestions for a better way to do things would be welcome.

AFAICS that's the *only* way to do it.

I disagree completely with the idea of forcing this behavior for all
datatypes.  It could only be sensible for fairly wide values; you don't
save enough to justify the lossiness otherwise.

It would be interesting to look into whether it could be driven on a
per-opclass basis.  Then you could have, eg, "text_lossy_hash_ops"
as a non-default opclass the DBA could select if he wanted this
behavior.  (The code could perhaps use the amopreqcheck flag to tell
it which way to behave.)  If that seems unworkable, I'd prefer to see us
set this up as a new index AM type, which would share a lot of code with
the old.

[ BTW, posting patches to pgsql-general seems pretty off-topic. ]

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Guillaume Smet
Дата:
Сообщение: Re: Bad plan after vacuum analyze
От: Greg Stark
Дата:
Сообщение: Re: Partitioning / Clustering