Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tables used in query not listed in EXPLAIN

Tables used in query not listed in EXPLAIN

From
Alvaro Melo
Date:
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
alvaro@atua.com.br   http://www.atua.com.br
(54) 9976-0106       (54) 3045-4144

Re: Tables used in query not listed in EXPLAIN

From
Tom Lane
Date:
Alvaro Melo <al_nunes@atua.com.br> writes:
> 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