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
Re: [GENERAL] Creating an index alters the results returned |
Список | 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 по дате отправления: