gist index woes

From: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: gist index woes
Date: 2004-09-21 16:14:14
Message-ID: opseop10fkcq72hf@musicbox
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Trying to build a gist index on a column in a table.
The table contains 100k rows.
The column is an integer[]. Each row contains about 20-30 distinct values
chosen between 1 and 437.
Aim : search the arrays with the gist integer array operators @ etc.

Creating the index with gist__int_ops takes forever and, after something
like one hour, fills the disk to the brim. There is about 4G free space on
the partition and it eats it completely.

Doing the same with only 10k rows takes forever too. Bumping up sort_mem
to 128 Meg does nothing. Only way to make it work is to create the index
on an empty table, and insert the rows afterwards. I only tried 10K rows
as disk space consumption is alarming.

Search speed in this 10k rows using the index is a lot slower (10x) than
sequential scan.

A problem with this contrib module ?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kundham Saare 2004-09-21 16:16:39 proxying connections
Previous Message Greg Stark 2004-09-21 16:01:40 Re: using database for queuing operations?