Tables used in query not listed in EXPLAIN

[Alvaro Melo]

Hi,

I have a peculiar situation here. I'm optimizing some queries and I
noticed that EXPLAIN doesn't always show the tables involved in the
query. Below is a little example, but I have other examples with longer
queries. Note that the tables tabela_documento_fiscal and pessoa aren't
considered on it's output. Am I missing someting or it might be a bug?

Details:
PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
Debian GNU/Linux 7.3 (wheezy)


vidal=> EXPLAIN
vidal-> SELECT COUNT(*)
vidal-> FROM documento_eletronico de
vidal-> ;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=4856.12..4856.14 rows=1 width=0)
-> Seq Scan on documento_eletronico de (cost=0.00..4606.50
rows=99850 width=0)

vidal=> EXPLAIN
vidal-> SELECT COUNT(*)
vidal-> FROM documento_eletronico de
vidal-> LEFT OUTER JOIN tabela_documento_fiscal tdf ON
tdf.cd_documento = de.cd_documento
vidal-> ;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=4856.12..4856.14 rows=1 width=4)
-> Seq Scan on documento_eletronico de (cost=0.00..4606.50
rows=99850 width=4)
(2 rows)

vidal=> EXPLAIN SELECT COUNT(*)
FROM documento_eletronico de
LEFT OUTER JOIN tabela_documento_fiscal tdf ON tdf.cd_documento =
de.cd_documento
LEFT OUTER JOIN pessoa pc ON pc.cd_pessoa =
de.cd_pessoa_usuario
;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=4856.12..4856.14 rows=1 width=8)
-> Seq Scan on documento_eletronico de (cost=0.00..4606.50
rows=99850 width=8)
(2 rows)

vidal=> EXPLAIN
vidal-> SELECT * FROM pessoa;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on pessoa (cost=0.00..3102.07 rows=133007 width=117)
--
Álvaro Nunes Melo Atua Sistemas de Informação
***@atua.com.br http://www.atua.com.br
(54) 9976-0106 (54) 3045-4144

[Tom Lane]

Post by Alvaro Melo
I have a peculiar situation here. I'm optimizing some queries and I
noticed that EXPLAIN doesn't always show the tables involved in the
query. Below is a little example, but I have other examples with longer
queries. Note that the tables tabela_documento_fiscal and pessoa aren't
considered on it's output. Am I missing someting or it might be a bug?

It looks to me like it's optimizing away the left joins, presumably
on the grounds that they join to a unique key so there can't be more
than one matching row --- and this query has no need to know exactly
which row, if any, matches.

regards, tom lane