> -----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.