From: | Clemens Eisserer <linuxhippy(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Creating an index alters the results returned |
Date: | 2014-01-03 19:59:54 |
Message-ID: | CAFvQSYSLkN5D4Up=0wyQDRfJr2vvQZeWa=BaZeSJaUf1heiUgg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general 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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Jungwirth | 2014-01-03 20:02:02 | Re: Suddenly all tables were gone |
Previous Message | Tom Lane | 2014-01-03 19:42:36 | Re: Suddenly all tables were gone |
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2014-01-03 20:42:03 | Re: Creating an index alters the results returned |
Previous Message | dmp | 2014-01-03 01:09:30 | Re: Release Notes? |