NumberFour's questions

No Postgres 9.5, faço o seguinte para converter a timestamptzpara o fuso horário UTC:

SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC'

isso gera corretamente 2016-02-20 14:15:54.000495como timestamp without timezone.

Eu gostaria de armazenar esse valor em uma timestamptzcoluna. No entanto, quando eu faço o seguinte:

SELECT (TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC')::timestamptz

Eu esperaria obter 2016-02-20 14:15:54.000495como timestamptz.

No entanto, esta consulta me dá 2016-02-20 14:15:54.000495+01como timestamptz, que infelizmente nem representa a hora original.

Qual é a razão de tal comportamento? Como faço isso funcionar corretamente?

Com o Postgres 9.4, estou fazendo a seguinte consulta com bastante frequência:

SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

Então resolvi criar uma view:

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
ORDER BY recipient, timestamp DESC;

Acabei de perceber que, se consultar minha visão SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB, obterei um desempenho significativamente pior.

Fazendo EXPLAIN ANALYZEem ambas as consultas, descobri que o motivo é que o banco de dados no segundo caso traz todos os registros, faz a junção à esquerda e aplica a WHEREcláusula. Em outras palavras, a WHEREcláusula não é inserida na consulta da exibição. Também tentei remover o ORDER BYda exibição, mas ainda assim o banco de dados executa os LEFT JOINdados completos em vez do conjunto filtrado.

Qual é a razão deste comportamento? Existe uma maneira de obter um desempenho comparável ao usar a visualização?

Talvez esta seja uma pergunta idiota para iniciantes, mas não consigo encontrar uma resposta em nenhum lugar. Em todos os lugares eu li sobre o Transaction Isolationque resolve a visibilidade dos dados dentro das transações simultâneas. Minha preocupação é o comportamento dentro de uma única transação.

Se eu iniciar uma transação, inserir alguns dados, poderei selecioná-los logo após - ainda dentro da mesma transação, mas não confirmada? Se sim, esse comportamento pode ser alterado de forma semelhante ao Isolamento de Transação mencionado no caso de transações simultâneas?

Para ser específico, estou direcionando o PostgreSQL 9.4.