From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Janek Sendrowski <janek12(at)web(dot)de>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Suitable Index for my Table |
Date: | 2013-11-04 18:40:43 |
Message-ID: | 1383590443.49877.YahooMailNeo@web162906.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Janek Sendrowski <janek12(at)web(dot)de> wrote:
> I've got a table with many Values of the Type REAL.
> These are my metric distances or my pivots to my sentences.
> The table looks like this:
>
> ID INTEGER, distance1 REAL, distance2 REAL, distance3 REAL,
> distance4 REAL, ..., distance24 REAL
It should always raise a big red flag when you see column names
with numeric suffixes like that. Usually these represent data
which should be normalized out to another table, or possibly
represented by a single column which is an array, hstore, or json
type.
> The range of the Value is in between 0 and 1. So it looks like
> this 0.196 or 0.891
>
> That my query
>
> WHERE value BETWEEN (distance1 - radius) AND (distance1 + radius)
> WHERE value BETWEEN (distance2 - radius) AND (distance2 + radius)
> WHERE value BETWEEN (distance3 - radius) AND (distance3 + radius)
> WHERE value BETWEEN (distance4 - radius) AND (distance4 + radius)
> ...
Are those the WHERE clauses of four different queries, or did you
mean for those to be four criteria on a single query? It would be
better to show an actual, working query and table layout, so people
have a more clear idea of the problem they are being asked to help
solve.
> Now I'm searching for a suitable index.
A btree index on each distance column, maybe?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2013-11-04 18:42:14 | Re: Suitable Index for my Table |
Previous Message | Jeff Amiel | 2013-11-04 18:38:27 | Re: table lock when where clause uses unique constraing instead of primary key. |