Hi,
I have a table defined as:
CREATE TABLE IF NOT EXISTS values
(
time BIGINT NOT NULL,
timeend BIGINT NOT NULL,
value VARCHAR NOT NULL,
PRIMARY KEY (time)
);
CREATE UNIQUE INDEX IF NOT EXISTS i_value_timeend on values (timeend);
The table is filled with values that are valid fo
I assume that you meant to write
Where time>=? And timeend wrote:
> Hi,
>
> I have a table defined as:
>
> CREATE TABLE IF NOT EXISTS values
> (
> time BIGINT NOT NULL,
> timeend BIGINT NOT NULL,
> value VARCHAR NOT NULL,
> PRIMARY KEY (time)
> );
> CREATE UNIQUE INDEX IF NOT EXIS
The where condition is correct. You could also rewrite it which might be
better understandable:
where between time and timeend-1
Unfortunatly there is no maximum gap so an optimiziation with time < ?+x is
not possible.
I will check the explain analyze statement.
On Thursday, February 26, 201
For a table with about 35000 rows explain analyze tells:
SELECT
> XVALUES.TIME,
> XVALUES.TIMEEND,
> XVALUES.VALUE
> FROM PUBLIC.XVALUES
> /* PUBLIC.PRIMARY_KEY_C: TIME <= 1424939489948 */
> /* scanCount: 35122 */
> WHERE (TIME <= 1424939489948)
> AND ((TIMEEND - 1) >= 1424
Hi,
I think for range conditions would be better estimate the cost on the basis
of histograms. However, at this moment the H2 doesn't make use of
histograms to estimate its costs. Evaluating postgres with your test-case,
I verified that for expressions like 'time > VALUE AND timeend < VALUE',
the
It's a bit of a hack, but what I can suggest is this:
do your query like this:
SELECT * FROM xvalues WHERE time <= x and time < (x + 50) and timeend
>= (x + 1 + 50)
Where 50 is a value calculated to give you the complete range 99.999% of
the time.
When you detect that you might possibly not
Hi,
I would consider using a spatial index.
I think no matter what you do, regular b-tree indexes can't be efficient if
you have many (millions of) entries: no matter what index you pick, some
queries would still scan about 50% of all entries.
Maybe you want to ask this question at StackOverflow
