Creating an index alters the results returned

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Clemens Eisserer
Sent: Friday, January 03, 2014 3:00 PM
To: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org
Subject: [GENERAL] Creating an index alters the results returned

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

The fact that it works as expected in pgadmin, probably indicates that the problem is on client/jdbc side.
Turn on backend logging (log_statement = 'all') for your jdbc connection, and after executing your query (through jdbc) look for it in pg_log.
You probably will find it "slightly" different from original. I'm not using jdbc, so can't commect on why this could happen.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, please check the server logs. There should be no reason whatsoever
that JDBC would effect the results if the query is the same

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Jan 3, 2014 at 3:42 PM, Igor Neyman <ineyman@perceptron.com> wrote:

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@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jan 3, 2014 at 5:55 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

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?

This brings up the point as well, don't use floating point numbers if
you want precise answers.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Regardless of type used… the driver and server should agree on the results.

On Jan 3, 2014, at 6:06 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Fri, Jan 3, 2014 at 5:55 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

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?

This brings up the point as well, don't use floating point numbers if
you want precise answers.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc