Re: Suitable Index for my Table - Mailing list pgsql-general

From Janek Sendrowski
Subject Re: Suitable Index for my Table
Date
Msg-id trinity-015c5003-ac70-487f-a1f5-bdda2ed90724-1383601469489@3capp-webde-bs05
Whole thread Raw
In response to Re: Suitable Index for my Table  (Martijn van Oosterhout <kleptog@svana.org>)
Responses Re: Suitable Index for my Table
Re: Suitable Index for my Table
List pgsql-general
Thanks for your Answers!
 
@Martijn van Oosterhout
 
I am always searching for single colums. The  values are different every time, because these are distances from my new
sentenceto my pivots. 
Does the Index support a query with this WHERE statement: WHERE value BETWEEN (distance1 - radius) AND (distance1 +
radius)?
 
@Kevin Grittner
okay, I will use arrays instead of multiple columns.
A working query looks like this:
SELECT id FROM distance WHERE
value BETWEEN (distance1 - radius) AND (distance1 + radius) AND
value BETWEEN (distance2 - radius) AND (distance2 + radius) AND
value BETWEEN (distance3 - radius) AND (distance3 + radius) AND
value BETWEEN (distance4 - radius) AND (distance4 + radius) AND
value BETWEEN (distance5 - radius) AND (distance5 + radius) AND
value BETWEEN (distance6 - radius) AND (distance6 + radius) AND
value BETWEEN (distance7 - radius) AND (distance7 + radius) AND
value BETWEEN (distance8 - radius) AND (distance8 + radius) AND
value BETWEEN (distance9 - radius) AND (distance9 + radius) AND
value BETWEEN (distance10 - radius) AND (distance10 + radius) AND
value BETWEEN (distance11 - radius) AND (distance11 + radius) AND
value BETWEEN (distance12 - radius) AND (distance12 + radius) AND
value BETWEEN (distance13 - radius) AND (distance13 + radius) AND
value BETWEEN (distance14 - radius) AND (distance14 + radius) AND
value BETWEEN (distance15 - radius) AND (distance15 + radius) AND
value BETWEEN (distance16 - radius) AND (distance16 + radius) AND
value BETWEEN (distance17 - radius) AND (distance17 + radius) AND
value BETWEEN (distance18 - radius) AND (distance18 + radius) AND
value BETWEEN (distance19 - radius) AND (distance19 + radius) AND
value BETWEEN (distance20 - radius) AND (distance20 + radius) AND
value BETWEEN (distance21 - radius) AND (distance22 + radius) AND
value BETWEEN (distance22 - radius) AND (distance23 + radius) AND
value BETWEEN (distance23 - radius) AND (distance24 + radius);
 
I'm not sure about the number of pivots yet. It can get bigger,
but this is a single query to determine the fitting sentences.
The query just returns the ID of the colum which is a foreign key to a sentence in another table.
The table distance contains the metric distance of every pivot to all of my sentences in the other table.
I haven't found yet the right pivots, but the distance should be between 0 and 1 in most cases, but it's also likely
thatit's 0. 
Until now It just does a Seq Scan, when I'm searching through the table 'distances'. I can show your the Query Plan, if
youwant. 
The number of rows which are resulting have a range of 0 until something like 100 for the begining.
 
Hope these were enough informations
 
Thank you for your help
Janek Sendrowki


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: table lock when where clause uses unique constraing instead of primary key.
Next
From: Jeff Amiel
Date:
Subject: Re: table lock when where clause uses unique constraing instead of primary key.