PostgreSQL HASH index

Question

Since we have moved to Asus Transformer tablets, app starts crashing. It throws out null exception when i click on ComboBox to change webcam. There is no error when loading webcams to the ComboBox

WPF:

        <ComboBox x:Name="cams" VerticalAlignment="Bottom" HorizontalAlignment="Center" Width="300" DisplayMemberPath="Name" Margin="10" FontSize="20" FontWeight="Bold"/>

C#

    private void InitializeComboBox()
    {
        cams.ItemsSource = webCameraControl.GetVideoCaptureDevices();

        if (cams.Items.Count > 0)
        {
            cams.SelectedItem = cams.Items[1];
            cams.SelectedIndex = 1;
        }
    }

As I said, there is no error when loading cams to ItemsSource, only when I click on it to change camera

Solution 1

Hashes are faster than B-Trees for cases where you have a known key value, especially a known unique value.

Hashes should be used if the column in question is never intended to be scanned comparatively with < or > commands.

Hashes are O(1) complexity, B-Trees are O(log n) complexity ( iirc ) , ergo, for large tables with unique entries, fetching an ITEM="foo", they will be the most efficient way of looking it up.

This is especially practical when these unique fields are used on a join condition.

Solution 2

It's better to use a Hash index for text columns that are searched using = operator only. For example a URL column which needs to be indexed for lookups.

A Hash index is approximately 30% the size of a B-Tree index for something like a URL.

The reduced size allows PostgreSQL to use it's cache memory (Aka, shared_buffers) more efficiently.

Solution 3

As http://www.postgresql.org/docs/9.2/static/sql-createindex.html point Hash index are still not WAL-safe; which means that they are not 100% reliable for crashes (index has to be reconstructed and wrong response could happen on replications). Check also http://www.postgresql.org/docs/9.1/static/wal-intro.html

Solution 4

I haven't tried this, but am considering this approach, to use hash indexes on non-logged temp tables.

My understanding is that they build faster, take less space & query slightly faster than b-tree.

According to this benchmark, hash indices are a bit faster and somewhat smaller than BTree indices. However, you can't make a unique hash index with them -- additionally they are not WAL-logged.

View more solutions