On 01/03/2014 11:59 AM, Clemens Eisserer wrote:
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?
Only thing I can think of is the JDBC driver and Postgres have a difference of opinion on the precision of double precision. What happens if you eliminate the double precision cast?
Another thought, what is the storage type for timestamps in this cluster? The default should be 8 byte integer, but floating point is also a possibility. To find out do:
pg_controldata PGDATA and look for: Date/time type storage: 64-bit integers
Thank you in advance, Clemens
-- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
