Select SIMPLE con TIMEZONES distintos no encuentra registros en 9.5 y 9.6 y si en 9.1

From: Alex Roca <arocag(at)gmail(dot)com>
To: Lista Postgres ES <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Select SIMPLE con TIMEZONES distintos no encuentra registros en 9.5 y 9.6 y si en 9.1
Date: 2016-10-18 14:38:28
Message-ID: CAKdjPnK4WRxd_rRC0Fv+tfRD5M4A2Cpgw9xNGOLoQt0dQxcrnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

Hola Lista, a ver si alguien me ilumina con este expediente X... Tengo
varias BBDD en postgres 9.1.X y las necesito a migrar a 9.5 o 9.6 pero
detecto un problema que no consigo solucionar.

Resumiendo la demostración que hay mas abajo, busco un registro en UTC de
una fecha sin problemas y al cambiar de TIMEZONE si la tabla tiene indice
en PG 9.5 o PG 9.6 NO encuentra el Registro.

La BBDD tanto en 9.1, 9.5 o 9.6 las tengo en UTC, al igual que el sistema
operativo.

En una 9.1 las siguientes sentencias FUNCIONAN con el cambio de TIMEZONE.

psql -d demo -U demo

Demostramos que estamos en UTC
demo=> *show timezone;*
TimeZone
----------
UTC
(1 row)

*create table a ( fecha timestamptz, numero numeric(10), codigo varchar(18)
);*

*alter table a add constraint a_pk primary key (fecha,numero,codigo);*
*insert into a values (current_timestamp, 2010, '004694052615675402');*

demo=> *select * from a;*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 13:37:52.287151+00 | 2010 | 004694052615675402
(1 row)

Localiza el registro sin problemas
demo=> *select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 13:37:52.287151+00' and numero = 2010 and codigo =
'004694052615675402';*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 13:37:52.287151+00 | 2010 | 004694052615675402

Este es el explain y usa el indice.
demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 13:37:52.287151+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using a_pk on a (cost=0.00..22.97 rows=1 width=46) (actual
time=0.015..0.017 rows=1 loops=1)
Index Cond: ((numero = 2010::numeric) AND ((codigo)::text =
'004694052615675402'::text))
Filter: (timezone('UTC'::text, fecha) = '2016-10-18
13:37:52.287151'::timestamp without time zone)
Total runtime: 0.049 ms
(4 rows)

Cambiamos el TIMEZONE y la MISMA consulta encuentra el registro con *ÉXITO* ya
que forzamos el TIMEZONE ( esto en PG9.5 - 9.6 NO FUNCIONA)
demo=> *SET TIMEZONE TO 'America/Santo_Domingo';*
select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT TIME
ZONE '2016-10-18 13:37:52.287151+00' and numero = 2010 and codigo =
'004694052615675402';
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 09:37:52.287151-04 | 2010 | 004694052615675402

demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 13:37:52.287151+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using a_pk on a (cost=0.00..22.97 rows=1 width=46) (actual
time=0.018..0.021 rows=1 loops=1)
Index Cond: ((numero = 2010::numeric) AND ((codigo)::text =
'004694052615675402'::text))
Filter: (timezone('UTC'::text, fecha) = '2016-10-18
13:37:52.287151'::timestamp without time zone)
Total runtime: 0.045 ms
(4 rows)

Misma prueba en postgres 9.5 o 9.6

psql -d demo -U demo

Demostramos que estamos en UTC
demo=> *show timezone;*
TimeZone
----------
UTC
(1 row)

*create table a ( fecha timestamptz, numero numeric(10), codigo varchar(18)
);*

*alter table a add constraint a_pk primary key (fecha,numero,codigo);*
*insert into a values (current_timestamp, 2010, '004694052615675402');*

demo=> *select * from a;*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 14:32:21.433333+00 | 2010 | 004694052615675402

localiza el registro sin problemas
demo=> *select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and numero = 2010 and codigo =
'004694052615675402';*
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 14:32:21.433333+00 | 2010 | 004694052615675402
(1 row)

Este es el explain y usa el indice.
demo=> *explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and numero =
2010 and codigo = '004694052615675402';*
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using a_pk on a (cost=0.15..8.17 rows=1 width=46) (actual
time=0.022..0.023 rows=1 loops=1)
Index Cond: ((fecha OPERATOR(pg_catalog.=) '2016-10-18
14:32:21.433333'::timestamp without time zone) AND (numero =
'2010'::numeric) AND (codigo = '004694052615675402'::text))
Heap Fetches: 1
Planning time: 0.135 ms
Execution time: 0.061 ms
(5 rows)

Cambiamos el TIMEZONE y la MISMA consulta encuentra el registro con *ÉXITO* ya
que forzamos el TIMEZONE ( esto en PG9.5 - 9.6 NO FUNCIONA)
demo=> *SET TIMEZONE TO 'America/Santo_Domingo';*

Dónde esta el registro ? No lo encuentra -- LA CULPA LA TIENE EL INDICE...
demo=> select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and numero = 2010 and codigo =
'004694052615675402';
fecha | numero | codigo
-------+--------+--------
(0 rows)

Como podemos ver usa la PK para NO Localizar el registro
demo=> explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and numero =
2010 and codigo = '004694052615675402';

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using a_pk on a (cost=0.15..8.17 rows=1 width=46) (actual
time=0.013..0.013 rows=0 loops=1)
Index Cond: ((fecha OPERATOR(pg_catalog.=) '2016-10-18
14:32:21.433333'::timestamp without time zone) AND (numero =
'2010'::numeric) AND (codigo = '004694052615675402'::text))
Heap Fetches: 0
Planning time: 0.120 ms
Execution time: 0.041 ms
(5 rows)

Borramos el índice
demo=> alter table a drop constraint a_pk;
ALTER TABLE

Buscamos con EXITO sin INDICE
demo=> select * from a where fecha AT TIME ZONE 'UTC' = TIMESTAMP WITHOUT
TIME ZONE '2016-10-18 14:32:21.433333+00' and to_varchar(numero) = '2010'
and codigo = '004694052615675402';
fecha | numero | codigo
-------------------------------+--------+--------------------
2016-10-18 10:32:21.433333-04 | 2010 | 004694052615675402
(1 row)

Demostración que ahora lo localiza con un secuencial Scan
demo=> explain analyze select * from a where fecha AT TIME ZONE 'UTC' =
TIMESTAMP WITHOUT TIME ZONE '2016-10-18 14:32:21.433333+00' and
to_varchar(numero) = '2010' and codigo = '004694052615675402';

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on a (cost=10000000000.00..10000000037.50 rows=1 width=46)
(actual time=0.018..0.020 rows=1 loops=1)
Filter: (((fecha)::timestamp without time zone = '2016-10-18
14:32:21.433333'::timestamp without time zone) AND ((codigo)::text =
'004694052615675402'::text) AND (((numero)::character varying(20))::text =
'2010'::text))
Planning time: 0.141 ms
Execution time: 0.048 ms
(4 rows)

Siento el tocho pero no doy con la solución. He probado reindexar ,
analizar estadísticas y nada, cuando usa el índice no encuentro el registro.

Muchas Gracias.
Alex.

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message Manuel Aller 2016-10-18 15:10:03 Re: Cambiar motor de disco en Ubuntu
Previous Message Jared Lopez 2016-10-16 13:49:36 Re: Herramientas para testing de base de datos.