Обсуждение: How can I make PosgreSQL use an Index ?

Поиск
Список
Период
Сортировка

How can I make PosgreSQL use an Index ?

От
Rhaoni Chiu Pereira
Дата:
Hi list,

    I have a table like this:

CREATE TABLE "gsames00" (
        "ano_mes" varchar(6) NOT NULL,
        "descricao" varchar(30),
        PRIMARY KEY ("ano_mes")
);

and an index like this:

CREATE INDEX GSAMES01 ON  GSAMES00 (ANO_MES);

  When I run a explain analyze with this where clause:

   ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...

   ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says:

...
 ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372 width=10)
(actual time=0.01..0.96 rows=372 loops=19923)
...

   So it is not using the index, and it makes the query too slow to return the
result. If a run the same query without this clause it gets about 1 minute
faster. You you're wondering : If you can run this query without this clause,
Why don't you take it out ?
   I must use it because this query is created by a BI software  and to
change it, I'll have to make a lot of changes in the BI software source. In the
Oracle DB it works fine 'cuz Oracle use the index and do it instantly.
   Any suggestion on how to force PostgreSQL tu use this index ???

Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122






Re: How can I make PosgreSQL use an Index ?

От
Tom Lane
Дата:
Rhaoni Chiu Pereira <rhaoni@sistemica.info> writes:
>   When I run a explain analyze with this where clause:
>    ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
> ...
>  ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372 width=10)
> (actual time=0.01..0.96 rows=372 loops=19923)
> ...

If you're not going to show us the whole query and the whole EXPLAIN
output, you're going to get equally incomplete answers.  I will say
though that forcing an index instead of a seqscan on a 372-row table
isn't likely to be a magic bullet.  You probably need a better join
plan.

Please post fuller details on pgsql-performance.  You might save some
time by reading the posting tips first ...
http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines

            regards, tom lane

Re: How can I make PosgreSQL use an Index ?

От
Gaetano Mendola
Дата:
Rhaoni Chiu Pereira wrote:

> Hi list,
>
>     I have a table like this:
>
> CREATE TABLE "gsames00" (
>         "ano_mes" varchar(6) NOT NULL,
>         "descricao" varchar(30),
>         PRIMARY KEY ("ano_mes")
> );
>
> and an index like this:
>
> CREATE INDEX GSAMES01 ON  GSAMES00 (ANO_MES);
>
>   When I run a explain analyze with this where clause:
>
>    ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
>
>    ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says:
>
> ...
>  ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372 width=10)
> (actual time=0.01..0.96 rows=372 loops=19923)
> ...

looking at the cost I guess that you never run vacuum analyze on your
table, do it and let us know.


Regards
Gaetano Mendola


Re: How can I make PosgreSQL use an Index ?

От
Rhaoni Chiu Pereira
Дата:
Her goes:

Query:

SELECT /*+  */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)),       '+', NVL(ftnfpr00.qtde_duzias,0),       0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+',
NVL(ftnfpr00.vlr_liquido,0), 0) ) ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto ,
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
                         SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) )
FROM
ftprod00 ,
ftnfco00 ,
ftcgma00 ,
ftcgca00 ,
ftspro00 ,
ftclcr00 ,
gsames00 ,
ftcofi00 ,
ftrepr00 ,
gsesta00 ,
ftsupv00 ,
ftgrep00 ,
ftclgr00 ,
ftband00 ,
fttcli00 ,
ftredc00 ,
ftnfpr00
WHERE
ftnfco00.emp = 909 AND
ftnfpr00.fil IN ('101') AND
ftnfco00.situacao_nf = 'N' AND
ftnfco00.data_emissao >= CAST('01-JAN-2003' AS DATE) AND
ftnfco00.data_emissao <= CAST('31-MAR-2003' AS DATE) AND
ftcofi00.grupo_faturamento >= '01' AND
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND
ftcgma00.emp    = ftprod00.emp AND
ftcgma00.fil    = ftprod00.fil AND
ftcgma00.codigo = ftprod00.cla_marca AND
ftcgca00.emp    = ftprod00.emp AND
ftcgca00.fil    = ftprod00.fil AND
ftcgca00.codigo = ftprod00.cla_categoria AND
ftspro00.emp    = ftprod00.emp AND
ftspro00.fil    = ftprod00.fil AND
ftspro00.codigo = ftprod00.situacao AND
ftclcr00.emp           = ftnfco00.emp AND
ftclcr00.fil           = ftnfco00.empfil AND
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND
ftclcr00.codigo        = ftnfco00.cod_cliente AND
gsames00.ano_mes       = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND
ftcofi00.emp           = ftnfco00.emp AND
ftcofi00.fil           = ftnfco00.empfil AND
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND
ftrepr00.emp           = ftnfco00.emp AND
ftrepr00.fil           = ftnfco00.empfil AND
ftrepr00.codigo_repr   = ftnfco00.cod_repres AND
gsesta00.estado_sigla  = ftnfco00.estado_cliente AND
ftsupv00.emp               = ftrepr00.emp AND
ftsupv00.fil               = ftrepr00.fil AND
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND
ftgrep00.emp               = ftrepr00.emp AND
ftgrep00.fil               = ftrepr00.fil AND
ftgrep00.codigo_grupo_rep  = ftrepr00.codigo_grupo_rep AND
ftclgr00.emp               = ftclcr00.emp AND
ftclgr00.fil               = ftclcr00.fil AND
ftclgr00.codigo            = ftclcr00.codigo_grupo_cliente AND
ftband00.emp               = ftclcr00.emp AND
ftband00.fil               = ftclcr00.fil AND
ftband00.codigo            = ftclcr00.bandeira_cliente AND
fttcli00.emp               = ftclcr00.emp AND
fttcli00.fil               = ftclcr00.fil AND
fttcli00.cod_tipocliente   = ftclcr00.codigo_tipo_cliente AND
ftredc00.emp               = ftclcr00.emp AND
ftredc00.fil               = ftclcr00.fil AND
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND
ftredc00.codigo_rede       = ftclcr00.codigo_rede AND
gsesta00.estado_sigla      = ftclcr00.emp_estado AND
ftnfco00.emp           = ftnfpr00.emp AND
ftnfco00.fil           = ftnfpr00.fil AND
ftnfco00.nota_fiscal   = ftnfpr00.nota_fiscal AND
ftnfco00.serie         = ftnfpr00.serie AND
ftnfco00.data_emissao  = ftnfpr00.data_emissao AND
ftprod00.emp           = ftnfpr00.emp AND
ftprod00.fil           = ftnfpr00.empfil AND
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND
ftprod00.codigo_produto= ftnfpr00.cod_produto
GROUP BY
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto



Explain:



                       QUERY
PLAN


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------
 Aggregate  (cost=100027780.66..100027780.69 rows=1 width=818) (actual
time=101278.24..105839.69 rows=363 loops=1)
   ->  Group  (cost=100027780.66..100027780.68 rows=1 width=818) (actual
time=101272.08..101761.18 rows=19923 loops=1)
         ->  Sort  (cost=100027780.66..100027780.67 rows=1 width=818) (actual
time=101272.05..101299.09 rows=19923 loops=1)
               Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento,
((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text),
ftprod00.descricao_produto
               ->  Nested Loop  (cost=100025960.94..100027780.65 rows=1
width=818) (actual time=3476.87..99606.77 rows=19923 loops=1)
                     Join Filter: (("outer".emp = "inner".emp) AND ("outer".fil
= "inner".fil) AND ("outer".codigo_supervisor = "inner".codigo_supervisor) AND
("outer".codigo_grupo_rep = "inner".codigo_grupo_rep))
                     ->  Nested Loop  (cost=100025960.94..100027775.22 rows=1
width=765) (actual time=3476.74..97802.69 rows=19923 loops=1)
                           Join Filter: (("inner".ano_mes)::text = to_char
("outer".data_emissao, 'YYYYMM'::text))
                           ->  Nested Loop  (cost=25960.94..27762.92 rows=1
width=755) (actual time=3475.14..32090.12 rows=19923 loops=1)
                                 Join Filter: (("inner".emp = "outer".emp) AND
("outer".fil = "inner".fil) AND ("outer".codigo = "inner".cla_categoria) AND
("outer".codigo = "inner".cla_marca) AND ("outer".codigo = "inner".situacao))
                                 ->  Nested Loop  (cost=25960.94..27705.22
rows=10 width=665) (actual time=3474.12..17734.21 rows=199230 loops=1)
                                       Join Filter: (("outer".emp
= "inner".emp) AND ("inner".fil = "outer".fil))
                                       ->  Nested Loop
(cost=25960.94..27699.30 rows=1 width=638) (actual time=3474.02..6030.09
rows=19923 loops=1)
                                             Join Filter: (("inner".emp
= "outer".emp) AND ("inner".empfil = "outer".fil))
                                             ->  Merge Join
(cost=25960.94..26128.25 rows=265 width=526) (actual time=3473.78..3841.18
rows=6358 loops=1)
                                                   Merge Cond: (("outer".emp
= "inner".emp) AND ("outer".fil = "inner".fil) AND ("outer".codigo_fiscal
= "inner".cod_fiscal))
                                                   ->  Index Scan using
ftcofi01 on ftcofi00  (cost=0.00..151.73 rows=72 width=52) (actual
time=0.15..6.40 rows=64 loops=1)
                                                         Filter:
((grupo_faturamento >= '01'::character varying) AND ((atual_fatura
= '+'::character varying) OR (atual_fatura = '-'::character varying) OR
(nf_prodgratis = 'S'::character varying)))
                                                   ->  Sort
(cost=25960.94..25965.34 rows=1760 width=474) (actual time=3471.17..3486.98
rows=7666 loops=1)
                                                         Sort Key:
ftnfco00.emp, ftredc00.fil, ftnfco00.cod_fiscal
                                                         ->  Nested Loop
(cost=25687.75..25866.07 rows=1760 width=474) (actual time=2981.05..3241.15
rows=7666 loops=1)
                                                               Join Filter:
(("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil) AND
("outer".codigo = "inner".codigo_grupo_cliente))
                                                               ->  Index Scan
using ftclgr01 on ftclgr00  (cost=0.00..4.68 rows=1 width=32) (actual
time=0.04..0.06 rows=1 loops=1)
                                                               ->  Materialize
(cost=25830.59..25830.59 rows=1760 width=442) (actual time=2980.93..2990.31
rows=7666 loops=1)
                                                                     ->  Hash
Join  (cost=25687.75..25830.59 rows=1760 width=442) (actual
time=2507.55..2945.35 rows=7666 loops=1)
                                                                           Hash
Cond: ("outer".emp_estado = "inner".estado_sigla)
                                                                           ->
Nested Loop  (cost=25683.33..25790.98 rows=1760 width=436) (actual
time=2507.09..2711.66 rows=7666 loops=1)

 Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil))

 ->  Index Scan using ftgrep01 on ftgrep00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.05..0.07 rows=1 loops=1)

 ->  Materialize  (cost=25759.91..25759.91 rows=1760 width=404) (actual
time=2506.98..2516.14 rows=7666 loops=1)

       ->  Nested Loop  (cost=25683.33..25759.91 rows=1760 width=404) (actual
time=2288.68..2474.11 rows=7666 loops=1)

             Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil
= "outer".fil))

             ->  Index Scan using ftsupv01 on ftsupv00  (cost=0.00..4.68 rows=1
width=32) (actual time=0.04..0.05 rows=1 loops=1)

             ->  Materialize  (cost=25728.83..25728.83 rows=1760 width=372)
(actual time=2288.58..2297.79 rows=7666 loops=1)

                   ->  Merge Join  (cost=25683.33..25728.83 rows=1760
width=372) (actual time=2086.89..2265.03 rows=7666 loops=1)

                         Merge Cond: (("outer".emp = "inner".emp) AND
("outer".fil = "inner".fil) AND ("outer".cod_tipocliente
= "inner".codigo_tipo_cliente))

                         ->  Index Scan using fttcli01 on fttcli00
(cost=0.00..5.85 rows=17 width=33) (actual time=0.03..0.25 rows=17 loops=1)

                         ->  Sort  (cost=25683.33..25687.73 rows=1760
width=339) (actual time=2086.71..2095.86 rows=7666 loops=1)

                               Sort Key: ftnfco00.emp, ftredc00.fil,
ftclcr00.codigo_tipo_cliente

                               ->  Nested Loop  (cost=25389.10..25588.46
rows=1760 width=339) (actual time=1729.53..1897.73 rows=7666 loops=1)

                                     Join Filter: (("inner".emp = "outer".emp)
AND ("inner".fil = "outer".fil) AND ("outer".codigo = "inner".bandeira_cliente))

                                     ->  Index Scan using ftband01 on ftband00
(cost=0.00..4.68 rows=1 width=32) (actual time=0.04..0.06 rows=1 loops=1)

                                     ->  Materialize  (cost=25552.99..25552.99
rows=1760 width=307) (actual time=1729.44..1738.69 rows=7666 loops=1)

                                           ->  Nested Loop
(cost=25389.10..25552.99 rows=1760 width=307) (actual time=1566.24..1705.51
rows=7666 loops=1)

                                                 Join Filter: (("inner".emp
= "outer".emp) AND ("inner".fil = "outer".fil))

                                                 ->  Index Scan using ftcgma01
on ftcgma00  (cost=0.00..4.68 rows=1 width=32) (actual time=0.03..0.05 rows=1
loops=1)

                                                 ->  Materialize
(cost=25521.91..25521.91 rows=1760 width=275) (actual time=1566.16..1575.29
rows=7666 loops=1)

                                                       ->  Merge Join
(cost=25389.10..25521.91 rows=1760 width=275) (actual time=1320.59..1542.54
rows=7666 loops=1)

                                                             Merge Cond:
(("outer".codigo = "inner".cod_cliente) AND ("outer".emp_estado
= "inner".estado_cliente) AND ("outer".tipo_cadastro = "inner".tipo_cad_clicre)
AND ("outer".fil = "inner".empfil) AND ("outer".emp = "inner".emp))

                                                             ->  Sort
(cost=6241.05..6269.31 rows=11304 width=166) (actual time=1093.04..1105.44
rows=10478 loops=1)

                                                                   Sort Key:
ftclcr00.codigo, ftclcr00.emp_estado, ftclcr00.tipo_cadastro, ftredc00.fil,
ftredc00.emp

                                                                   ->  Merge
Join  (cost=3920.20..5480.05 rows=11304 width=166) (actual time=516.40..951.73
rows=10956 loops=1)

                                                                         Merge
Cond: (("outer".emp = "inner".emp) AND ("outer".fil = "inner".fil) AND
("outer".tipo_contribuinte = "inner".tipo_contribuinte) AND
("outer".codigo_rede = "inner".codigo_rede))

                                                                         ->
Merge Join  (cost=0.00..1256.74 rows=8906 width=72) (actual time=0.13..180.25
rows=8906 loops=1)


Merge Cond: ("outer".emp = "inner".emp)

                                                                               -
>  Index Scan using ftredc01 on ftredc00  (cost=0.00..1118.47 rows=8906
width=40) (actual time=0.05..72.02 rows=8906 loops=1)

                                                                               -
>  Index Scan using ftcgca01 on ftcgca00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.04..19.14 rows=1 loops=1)

                                                                         ->
Sort  (cost=3920.20..3947.59 rows=10956 width=94) (actual time=516.19..529.77
rows=10956 loops=1)


Sort Key: ftclcr00.emp, ftclcr00.fil, ftclcr00.tipo_contribuinte,
ftclcr00.codigo_rede

                                                                               -
>  Index Scan using ftclcr07 on ftclcr00  (cost=0.00..3185.08 rows=10956
width=94) (actual time=0.09..146.20 rows=10956 loops=1)

                                                             ->  Sort
(cost=19148.05..19167.27 rows=7688 width=109) (actual time=227.46..237.00
rows=7668 loops=1)

                                                                   Sort Key:
ftnfco00.cod_cliente, ftnfco00.estado_cliente, ftnfco00.tipo_cad_clicre,
ftnfco00.empfil, ftnfco00.emp

                                                                   ->  Index
Scan using ftnfco06 on ftnfco00  (cost=0.00..18651.88 rows=7688 width=109)
(actual time=0.16..116.43 rows=7668 loops=1)

                                                                         Index
Cond: ((emp = 909::numeric) AND (situacao_nf = 'N'::character varying) AND
(data_emissao >= '2002-10-01 00:00:00'::timestamp without time zone) AND
(data_emissao <= '2003-03-31 00:00:00'::timestamp without time zone))
                                                                           ->
Hash  (cost=4.33..4.33 rows=33 width=6) (actual time=0.23..0.23 rows=0 loops=1)

 ->  Index Scan using gsesta01 on gsesta00  (cost=0.00..4.33 rows=33 width=6)
(actual time=0.04..0.15 rows=33 loops=1)
                                             ->  Index Scan using ftnfpr05 on
ftnfpr00  (cost=0.00..5.91 rows=1 width=112) (actual time=0.06..0.15 rows=3
loops=6358)
                                                   Index Cond: (("outer".emp =
ftnfpr00.emp) AND ("outer".fil = ftnfpr00.fil) AND (ftnfpr00.fil =
101::numeric) AND ("outer".data_emissao = ftnfpr00.data_emissao) AND
("outer".nota_fiscal = ftnfpr00.nota_fiscal) AND ("outer".serie =
ftnfpr00.serie))
                                       ->  Index Scan using ftspro01 on
ftspro00  (cost=0.00..5.78 rows=10 width=27) (actual time=0.01..0.07 rows=10
loops=19923)
                                 ->  Index Scan using ftprod01 on ftprod00
(cost=0.00..5.74 rows=1 width=90) (actual time=0.04..0.05 rows=1 loops=199230)
                                       Index Cond: ((ftprod00.emp
= "outer".emp) AND (ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro
= "outer".tipo_cad_promat) AND (ftprod00.codigo_produto = "outer".cod_produto))
                           ->  Seq Scan on gsames00
(cost=100000000.00..100000006.72 rows=372 width=10) (actual time=0.01..0.96
rows=372 loops=19923)
                     ->  Index Scan using ftrepr01 on ftrepr00
(cost=0.00..5.41 rows=1 width=53) (actual time=0.04..0.05 rows=1 loops=19923)
                           Index Cond: ((ftrepr00.emp = "outer".emp) AND
(ftrepr00.fil = "outer".empfil) AND (ftrepr00.codigo_repr = "outer".cod_repres))
 Total runtime: 105885.43 msec
(75 rows)



The Oracle functions like NVL, DECODE, and others had been created in
PostgreSQL.



Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122



Citando Tom Lane <tgl@sss.pgh.pa.us>:

<> Rhaoni Chiu Pereira <rhaoni@sistemica.info> writes:
<> >   When I run a explain analyze with this where clause:
<> >    ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
<> > ...
<> >  ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372
<> width=10)
<> > (actual time=0.01..0.96 rows=372 loops=19923)
<> > ...
<>
<> If you're not going to show us the whole query and the whole EXPLAIN
<> output, you're going to get equally incomplete answers.  I will say
<> though that forcing an index instead of a seqscan on a 372-row table
<> isn't likely to be a magic bullet.  You probably need a better join
<> plan.
<>
<> Please post fuller details on pgsql-performance.  You might save some
<> time by reading the posting tips first ...
<> http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines
<>
<>             regards, tom lane
<>
<> ---------------------------(end of broadcast)---------------------------
<> TIP 2: you can get off all lists at once with the unregister command
<>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
<>


Re: How can I make PosgreSQL use an Index ?

От
Tom Lane
Дата:
Rhaoni Chiu Pereira <rhaoni@sistemica.info> writes:
> [ very large join plan ]

Sorry for not responding sooner --- somehow this got overlooked in my
inbox.  It looks to me like the big problem is that you have all these
nested-loop joins:

>                ->  Nested Loop  (cost=100025960.94..100027780.65 rows=1
> width=818) (actual time=3476.87..99606.77 rows=19923 loops=1)
>                      Join Filter: (("outer".emp = "inner".emp) AND ("outer".fil
> = "inner".fil) AND ("outer".codigo_supervisor = "inner".codigo_supervisor) AND
> ("outer".codigo_grupo_rep = "inner".codigo_grupo_rep))
>                      ->  Nested Loop  (cost=100025960.94..100027775.22 rows=1
> width=765) (actual time=3476.74..97802.69 rows=19923 loops=1)
>                            Join Filter: (("inner".ano_mes)::text = to_char
> ("outer".data_emissao, 'YYYYMM'::text))
>                            ->  Nested Loop  (cost=25960.94..27762.92 rows=1
> width=755) (actual time=3475.14..32090.12 rows=19923 loops=1)
>                                  Join Filter: (("inner".emp = "outer".emp) AND
> ("outer".fil = "inner".fil) AND ("outer".codigo = "inner".cla_categoria) AND
> ("outer".codigo = "inner".cla_marca) AND ("outer".codigo = "inner".situacao))
>                                  ->  Nested Loop  (cost=25960.94..27705.22
> rows=10 width=665) (actual time=3474.12..17734.21 rows=199230 loops=1)
>                                        Join Filter: (("outer".emp
> = "inner".emp) AND ("inner".fil = "outer".fil))
>                                        ->  Nested Loop
> (cost=25960.94..27699.30 rows=1 width=638) (actual time=3474.02..6030.09
> rows=19923 loops=1)
>                                              Join Filter: (("inner".emp
> = "outer".emp) AND ("inner".empfil = "outer".fil))

The reason the planner is choosing nestloop here is that it thinks there
are only a few rows involved (notice the estimated row counts are all "1"
or "10").  Nestloop is a fine join plan for small numbers of rows, but
it pretty well sucks for tens of thousands of rows which is what you've
actually got.  I am not sure why the row-count estimates are so far off,
but it could be because the planner is unaware of cross-column
correlations in your data.  Are the multiple join conditions actually
necessary, or are some of them redundant?

A quick and dirty thing you could try to see if the plan can be improved
is to set "enable_nestloop" off (do NOT set "enable_seqscan" off).

In the long run you want the planner to do better without such a
brute-force hack, though.  A simple answer is to boost the statistics
target on the join columns and re-analyze, but that may not help much
if the real issue is cross-column correlations.  A more invasive
solution is to reconsider your data design.  It looks like you have a
lot of multi-column join keys --- can you find ways to combine those
into single columns?  (As an example, I'd never build a table containing
separate date and time columns rather than a single timestamp column.)
I realize that this'd probably be a pain in the neck, but if you can do
it, it would simplify your queries as well as help the planner produce
better plans.

            regards, tom lane