Creating an index alters the results returned

Поиск
Список
Период
Сортировка
От Clemens Eisserer
Тема Creating an index alters the results returned
Дата
Msg-id CAFvQSYSLkN5D4Up=0wyQDRfJr2vvQZeWa=BaZeSJaUf1heiUgg@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] Creating an index alters the results returned  (Igor Neyman <ineyman@perceptron.com>)
Re: [GENERAL] Creating an index alters the results returned  (Adrian Klaver <adrian.klaver@gmail.com>)
Список pgsql-jdbc
Hi,

Running postgres 9.1.11 + postgresql-9.3-1100.jdbc41.jar, I experience
a strange phenomenon using indexes.

I have the following schema:
CREATE TABLE wplog (id serial NOT NULL,ts timestamp without time zone,
sensor1 real, sensor2 real, noiselevel smallint, CONSTRAINT wplog_pkey
PRIMARY KEY (id))

and execute the following query on it:

SELECT sensor1, sensor2, EXTRACT(EPOCH from ts) AS epoche FROM wplog
WHERE EXTRACT(EPOCH from ts) BETWEEN 1388712180::double precision AND
1388780572::double precision ORDER BY id

However, the results differ, depending on whether I've created an
index on ts or not:

With index:
ResultSet Size: 6651
minTS: 1388730187145     maxTs: 1388796688388  txdiff: 66501243

Without index:
ResultSet Size: 6830
minTS: 1388712182800     maxTs: 1388780567963  txdiff: 68385163

The index looks like: CREATE INDEX ON wplog (CAST(EXTRACT(EPOCH from
ts) AS double precision))

Even more puzzling to me is the fact, that I can only observe this
difference when using the JDBC driver,
using pgadmin to execute the query I get consistent results.

Is this behaviour expected?

Thank you in advance, Clemens


В списке pgsql-jdbc по дате отправления:

Предыдущее
От: dmp
Дата:
Сообщение: Re: Release Notes?
Следующее
От: Igor Neyman
Дата:
Сообщение: Re: [GENERAL] Creating an index alters the results returned