Обсуждение: Query Analyzing

I'm trying to figure out how to use explain to analyze my queries and speed
them up based on that information.  Are there any good resources on this out
there?  If not could someone look at this explain plan and tell me what I'm
looking at and why certain things are performing the way they are.

Query:
SELECT DISTINCT ALLFORMS.File__no, ALLFORMS.Mod, ALLFORMS.Frm_Wks,
ALLFORMS.ddf_type,
                AANDA.Rev_Date, to_char(AANDA.Prelim_Est, 'MM/DD/YY') AS
prelim_est,
                to_char(AANDA.Prelim_Rec, 'MM/DD/YY') AS prelim_rec,
                to_char(AANDA.Final_Est, 'MM/DD/YY') AS final_est,
                to_char(AANDA.Final_Rec, 'MM/DD/YY') AS final_rec,
                to_char(AANDA.InstrExp, 'MM/DD/YY') AS instrexp,
                to_char(AANDA.Lsr_to_GWood, 'MM/DD/YY') AS lsr_to_gwood,
                to_char(AANDA.Instr_Rec, 'MM/DD/YY') AS instr_rec,
                to_char(AANDA.GWood_Recd, 'MM/DD/YY') AS gwood_recd,
                to_char(aanda.m_w_inst_to_dev, 'MM/DD/YY') AS
m_w_inst_to_dev,
                AANDA.m_w_inst_status,
                to_char(AANDA.M_W_Lsr_To_Dev, 'MM/DD/YY') AS m_w_lsr_to_dev,
                AANDA.M_W_Lsr_Status, to_char(AANDA.Lsr_Sent, 'MM/DD/YY') AS
lsr_sent,
                to_char(AANDA.M_W_Lsr_App, 'MM/DD/YY') AS m_w_lsr_app,
                to_char(AANDA.InstPrelimExp, 'MM/DD/YY') AS instprelimexp,
                to_char(AANDA.InstPrelimRec, 'MM/DD/YY') AS instprelimrec,
                MODINFO.Team_Leader, MODINFO.Forms_Lead, MODINFO.Developer,
AANDA.plus,
                MODINFO.IDGAandAPOC, MODINFO.IDGAandALead,
MODINFO.LacerteLead,
                grdb.teamlead AS grdblacertelead, MODINFO.LacerteResearcher,
                grdb.developer AS grdblacertedeveloper, MODINFO.EFLead,
MODINFO.EFDeveloper,
                MODINFO.NGILead, MODINFO.NGIDeveloper,
                to_char(AANDA.LsctoDev, 'MM/DD/YY') AS lsctodev,
AANDA.LscStatus,
                to_char(AANDA.LscSent, 'MM/DD/YY') AS lscsent,
                to_char(AANDA.LscApp, 'MM/DD/YY') AS lscapp,
                grdb.busunit || grdb.state AS newlacertemodequiv,
                ALLFORMS.OSfirstchk, MODINFO.Module, ALLFORMS.Scannablechk,
                CASE WHEN comchk = true THEN 'C'
                     ELSE ''
                     END AS CT,
                ALLFORMS.Inactive, aanda_status_types.code,
                modinfo.proseries_actual_date, modinfo.turbotax_actual_date,
                modinfo.webturbotax_actual_date,
modinfo.lacerte_actual_date,
                tl.Extension as tl_extension, dev.Extension as
dev_extension,
                ll.Extension as ll_extension, lr.Extension as lr_extension,
        grdb.lacertefilename AS lacertename, grdb.lacerteformname,
                gd.extension AS grdblacertedeveloperextension,
                gl.extension AS grdblacerteleadextension
  FROM ((((((((MODINFO INNER JOIN
       (ALLFORMS INNER JOIN AANDA ON ALLFORMS.File__no = AANDA.File__no)
       ON MODINFO.Module = ALLFORMS.Mod) LEFT JOIN aanda_status_types
       ON AANDA.aanda_status_type_id =
aanda_status_types.aanda_status_type_id)
       LEFT JOIN Users AS tl ON MODINFO.Team_Leader = tl.name)
       LEFT JOIN Users AS dev ON MODINFO.Developer = dev.name)
       LEFT JOIN Users AS ll ON MODINFO.LacerteLead = ll.name)
       LEFT JOIN Users AS lr ON MODINFO.LacerteResearcher = lr.name)
       LEFT JOIN grdb ON allforms.file__no = grdb.intuitfilename)
       LEFT JOIN Users AS gd ON grdb.developer = gd.name)
       LEFT JOIN Users AS gl ON grdb.teamlead = gl.name
 WHERE ALLFORMS.Inactive Is Null;

Explain Plan:
Unique  (cost=3561.60..4057.61 rows=342 width=682)
  ->  Sort  (cost=3561.60..3561.60 rows=3421 width=682)
    ->  Hash Join  (cost=2140.54..2738.33 rows=3421 width=682)
      ->  Hash Join  (cost=2132.03..2669.96 rows=3421 width=656)
        ->  Merge Join  (cost=2123.52..2601.58 rows=3421 width=630)
          ->  Index Scan using grdb_intuitfilename_idx on grdb
(cost=0.00..429.67 rows=5312 width=75)
            ->  Sort  (cost=2123.52..2123.52 rows=3421 width=555)
              ->  Hash Join  (cost=609.03..1420.19 rows=3421 width=555)
                ->  Hash Join  (cost=600.52..1351.81 rows=3421 width=529)
                  ->  Hash Join  (cost=592.01..1283.44 rows=3421 width=503)
                    ->  Hash Join  (cost=583.49..1215.06 rows=3421
width=477)
                      ->  Hash Join  (cost=574.98..1146.69 rows=3421
width=451)
                        ->  Hash Join  (cost=573.88..1128.39 rows=3421
width=442)
                          ->  Hash Join  (cost=524.27..993.25 rows=3421
width=232)
                            ->  Seq Scan on aanda  (cost=0.00..112.29
rows=3529 width=180)
                            ->  Hash  (cost=422.54..422.54 rows=8292
width=52)
                              ->  Seq Scan on allforms  (cost=0.00..422.54
rows=8292 width=52)
                          ->  Hash  (cost=48.69..48.69 rows=369 width=210)
                            ->  Seq Scan on modinfo  (cost=0.00..48.69
rows=369 width=210)
                        ->  Hash  (cost=1.08..1.08 rows=8 width=9)
                          ->  Seq Scan on aanda_status_types
(cost=0.00..1.08 rows=8 width=9)
                      ->  Hash  (cost=7.81..7.81 rows=281 width=26)
                        ->  Seq Scan on users tl  (cost=0.00..7.81 rows=281
width=26)
                    ->  Hash  (cost=7.81..7.81 rows=281 width=26)
                      ->  Seq Scan on users dev  (cost=0.00..7.81 rows=281
width=26)
                  ->  Hash  (cost=7.81..7.81 rows=281 width=26)
                    ->  Seq Scan on users ll  (cost=0.00..7.81 rows=281
width=26)
                ->  Hash  (cost=7.81..7.81 rows=281 width=26)
                  ->  Seq Scan on users lr  (cost=0.00..7.81 rows=281
width=26)
          ->  Hash  (cost=7.81..7.81 rows=281 width=26)
            ->  Seq Scan on users gd  (cost=0.00..7.81 rows=281 width=26)
        ->  Hash  (cost=7.81..7.81 rows=281 width=26)
          ->  Seq Scan on users gl  (cost=0.00..7.81 rows=281 width=26)

In looking at this I see that my index on the grdb table is getting used,
but all of the other tables are being sequentially scanned.  All of the
joins are being done on primary key fields but they are all getting
sequentially scanned, is there something that I'm missing?

Again if you can point me to a good resource for learning this I'd
appreciate it.

Thanks,
Rob

On Wed, 3 Jul 2002 11:02:00 -0700, "Booth, Robert"
<Robert_Booth@intuit.com> wrote:
>In looking at this I see that my index on the grdb table is getting used,
>but all of the other tables are being sequentially scanned.  All of the
>joins are being done on primary key fields but they are all getting
>sequentially scanned, is there something that I'm missing?

Robert,

if you have not yet VACUUM ANALYZEd your db, now's the time to do it.

If you are on v7.2.x, use EXPLAIN ANALYZE;  it gives you not only the
estimated times and row counts, but also the actual numbers.

And finally, a sequential scan is not a bad thing per se.  If a large
fraction of the rows are to be fetched and if these rows are spread
all over the table, a sequential scan is faster than going through an
index and then again ending up reading (almost) all pages.

>  Seq Scan on users lr  (cost=0.00..7.81 rows=281 width=26)

How many rows are in users?  How many of them match your join
criteria?

Servus
 Manfred

> that's not easy;  let's try on.  Could you post these two EXPLAIN
> ANALYZE results to the -general?  Just for us to get a feeling ...
Query:
SELECT DISTINCT ALLFORMS.File__no, ALLFORMS.Mod, ALLFORMS.Frm_Wks,
ALLFORMS.ddf_type,
                AANDA.Rev_Date, to_char(AANDA.Prelim_Est, 'MM/DD/YY') AS
prelim_est,
                to_char(AANDA.Prelim_Rec, 'MM/DD/YY') AS prelim_rec,
                to_char(AANDA.Final_Est, 'MM/DD/YY') AS final_est,
                to_char(AANDA.Final_Rec, 'MM/DD/YY') AS final_rec,
                to_char(AANDA.InstrExp, 'MM/DD/YY') AS instrexp,
                to_char(AANDA.Lsr_to_GWood, 'MM/DD/YY') AS lsr_to_gwood,
                to_char(AANDA.Instr_Rec, 'MM/DD/YY') AS instr_rec,
                to_char(AANDA.GWood_Recd, 'MM/DD/YY') AS gwood_recd,
                to_char(aanda.m_w_inst_to_dev, 'MM/DD/YY') AS
m_w_inst_to_dev,
                AANDA.m_w_inst_status,
                to_char(AANDA.M_W_Lsr_To_Dev, 'MM/DD/YY') AS m_w_lsr_to_dev,
                AANDA.M_W_Lsr_Status, to_char(AANDA.Lsr_Sent, 'MM/DD/YY') AS
lsr_sent,
                to_char(AANDA.M_W_Lsr_App, 'MM/DD/YY') AS m_w_lsr_app,
                to_char(AANDA.InstPrelimExp, 'MM/DD/YY') AS instprelimexp,
                to_char(AANDA.InstPrelimRec, 'MM/DD/YY') AS instprelimrec,
                MODINFO.Team_Leader, MODINFO.Forms_Lead, MODINFO.Developer,
AANDA.plus,
                MODINFO.IDGAandAPOC, MODINFO.IDGAandALead,
MODINFO.LacerteLead,
                grdb.teamlead AS grdblacertelead, MODINFO.LacerteResearcher,
                grdb.developer AS grdblacertedeveloper, MODINFO.EFLead,
MODINFO.EFDeveloper,
                MODINFO.NGILead, MODINFO.NGIDeveloper,
                to_char(AANDA.LsctoDev, 'MM/DD/YY') AS lsctodev,
AANDA.LscStatus,
                to_char(AANDA.LscSent, 'MM/DD/YY') AS lscsent,
                to_char(AANDA.LscApp, 'MM/DD/YY') AS lscapp,
                grdb.busunit || grdb.state AS newlacertemodequiv,
                ALLFORMS.OSfirstchk, MODINFO.Module, ALLFORMS.Scannablechk,
                CASE WHEN comchk = true THEN 'C'
                     ELSE ''
                     END AS CT,
                ALLFORMS.Inactive, aanda_status_types.code,
                modinfo.proseries_actual_date, modinfo.turbotax_actual_date,
                modinfo.webturbotax_actual_date,
modinfo.lacerte_actual_date,
                tl.Extension as tl_extension, dev.Extension as
dev_extension,
                ll.Extension as ll_extension, lr.Extension as lr_extension,
                grdb.lacertefilename AS lacertename, grdb.lacerteformname,
                gd.extension AS grdblacertedeveloperextension,
                gl.extension AS grdblacerteleadextension
  FROM ((((((((MODINFO INNER JOIN
       (ALLFORMS INNER JOIN AANDA ON ALLFORMS.File__no = AANDA.File__no)
       ON MODINFO.Module = ALLFORMS.Mod) LEFT JOIN aanda_status_types
       ON AANDA.aanda_status_type_id =
aanda_status_types.aanda_status_type_id)
       LEFT JOIN Users AS tl ON MODINFO.Team_Leader = tl.name)
       LEFT JOIN Users AS dev ON MODINFO.Developer = dev.name)
       LEFT JOIN Users AS ll ON MODINFO.LacerteLead = ll.name)
       LEFT JOIN Users AS lr ON MODINFO.LacerteResearcher = lr.name)
       LEFT JOIN grdb ON allforms.file__no = grdb.intuitfilename)
       LEFT JOIN Users AS gd ON grdb.developer = gd.name)
       LEFT JOIN Users AS gl ON grdb.teamlead = gl.name
 WHERE ALLFORMS.Inactive Is Null;

Explain Analyze:
Unique  (cost=3510.89..4004.48 rows=340 width=683) (actual
time=20608.43..21148.51 rows=5721 loops=1)
  ->  Sort  (cost=3510.89..3510.89 rows=3404 width=683) (actual
time=20608.41..20688.00 rows=5721 loops=1)
        ->  Hash Join  (cost=852.50..2693.25 rows=3404 width=683) (actual
time=1607.52..18045.90 rows=5721 loops=1)
              ->  Hash Join  (cost=843.99..2625.17 rows=3404 width=657)
(actualtime=1594.90..14424.21 rows=5721 loops=1)
                    ->  Hash Join  (cost=835.47..2557.08 rows=3404
width=631) (actual time=1583.07..11301.51 rows=5721 loops=1)
                          ->  Hash Join  (cost=609.05..1418.36 rows=3404
width=556) (actual time=1033.09..7687.77 rows=2983 loops=1)
                                ->  Hash Join  (cost=600.54..1350.27
rows=3404 width=530) (actual time=1021.56..6627.42 rows=2983 loops=1)
                                      ->  Hash Join  (cost=592.02..1282.19
rows=3404 width=504) (actual time=1010.18..5674.81 rows=2983 loops=1)
                                            ->  Hash Join
(cost=583.51..1214.10 rows=3404 width=478) (actual time=999.13..4811.00
rows=2983 loops=1)
                                                  ->  Hash Join
(cost=575.00..1146.02 rows=3404 width=452) (actual time=987.95..4034.51
rows=2983 loops=1)
                                                        ->  Hash Join
(cost=573.90..1127.80 rows=3404 width=443) (actual time=986.92..3334.55
rows=2983 loops=1)
                                                              ->  Hash Join
(cost=524.29..993.09 rows=3404 width=233) (actual time=951.76..2567.33
rows=2985 loops=1)
                                                                    ->  Seq
Scan on aanda  (cost=0.00..112.30 rows=3530 width=180) (actual
time=0.20..301.30 rows=3530 loops=1)
                                                                    ->  Hash
(cost=422.64..422.64 rows=8259 width=53) (actual time=914.46..914.46 rows=0
loops=1)
                                                                          ->
Seq Scan on allforms  (cost=0.00..422.64 rows=8259 width=53) (actual
time=0.26..725.04 rows=8346 loops=1)
                                                              ->  Hash
(cost=48.69..48.69 rows=369 width=210) (actual time=34.66..34.66 rows=0
loops=1)
                                                                    ->  Seq
Scan on modinfo  (cost=0.00..48.69 rows=369 width=210) (actual
time=0.19..28.17 rows=369 loops=1)
                                                        ->  Hash
(cost=1.08..1.08 rows=8 width=9) (actual time=0.50..0.50 rows=0 loops=1)
                                                              ->  Seq Scan
on aanda_status_types  (cost=0.00..1.08 rows=8 width=9) (actual
time=0.23..0.40 rows=8 loops=1)
                                                  ->  Hash  (cost=7.81..7.81
rows=281 width=26) (actual time=10.62..10.62 rows=0 loops=1)
                                                        ->  Seq Scan on
users tl  (cost=0.00..7.81 rows=281 width=26) (actual time=0.05..6.83
rows=281 loops=1)
                                            ->  Hash  (cost=7.81..7.81
rows=281width=26) (actual time=10.47..10.47 rows=0 loops=1)
                                                  ->  Seq Scan on users dev
(cost=0.00..7.81 rows=281 width=26) (actual time=0.05..6.59 rows=281
loops=1)
                                      ->  Hash  (cost=7.81..7.81 rows=281
width=26) (actual time=10.67..10.67 rows=0 loops=1)
                                            ->  Seq Scan on users ll
(cost=0.00..7.81 rows=281 width=26) (actual time=0.05..6.75 rows=281
loops=1)
                                ->  Hash  (cost=7.81..7.81 rows=281
width=26) (actual time=10.86..10.86 rows=0 loops=1)
                                      ->  Seq Scan on users lr
(cost=0.00..7.81 rows=281 width=26) (actual time=0.06..6.85 rows=281
loops=1)
                          ->  Hash  (cost=145.14..145.14 rows=5314 width=75)
(actual time=509.09..509.09 rows=0 loops=1)
                                ->  Seq Scan on grdb  (cost=0.00..145.14
rows=5314 width=75) (actual time=0.17..317.69 rows=5314 loops=1)
                    ->  Hash  (cost=7.81..7.81 rows=281 width=26) (actual
time=10.96..10.96 rows=0 loops=1)
                          ->  Seq Scan on users gd  (cost=0.00..7.81
rows=281 width=26) (actual time=0.05..6.99 rows=281 loops=1)
              ->  Hash  (cost=7.81..7.81 rows=281 width=26) (actual
time=11.06..11.06 rows=0 loops=1)
                    ->  Seq Scan on users gl  (cost=0.00..7.81 rows=281
width=26) (actual time=0.14..7.09 rows=281 loops=1)
Total runtime: 21238.31 msec

Query - Without Users Table:
SELECT DISTINCT ALLFORMS.File__no, ALLFORMS.Mod, ALLFORMS.Frm_Wks,
ALLFORMS.ddf_type,
                AANDA.Rev_Date, to_char(AANDA.Prelim_Est, 'MM/DD/YY') AS
prelim_est,
                to_char(AANDA.Prelim_Rec, 'MM/DD/YY') AS prelim_rec,
                to_char(AANDA.Final_Est, 'MM/DD/YY') AS final_est,
                to_char(AANDA.Final_Rec, 'MM/DD/YY') AS final_rec,
                to_char(AANDA.InstrExp, 'MM/DD/YY') AS instrexp,
                to_char(AANDA.Lsr_to_GWood, 'MM/DD/YY') AS lsr_to_gwood,
                to_char(AANDA.Instr_Rec, 'MM/DD/YY') AS instr_rec,
                to_char(AANDA.GWood_Recd, 'MM/DD/YY') AS gwood_recd,
                to_char(aanda.m_w_inst_to_dev, 'MM/DD/YY') AS
m_w_inst_to_dev,
                AANDA.m_w_inst_status,
                to_char(AANDA.M_W_Lsr_To_Dev, 'MM/DD/YY') AS m_w_lsr_to_dev,
                AANDA.M_W_Lsr_Status, to_char(AANDA.Lsr_Sent, 'MM/DD/YY') AS
lsr_sent,
                to_char(AANDA.M_W_Lsr_App, 'MM/DD/YY') AS m_w_lsr_app,
                to_char(AANDA.InstPrelimExp, 'MM/DD/YY') AS instprelimexp,
                to_char(AANDA.InstPrelimRec, 'MM/DD/YY') AS instprelimrec,
                MODINFO.Team_Leader, MODINFO.Forms_Lead, MODINFO.Developer,
AANDA.plus,
                MODINFO.IDGAandAPOC, MODINFO.IDGAandALead,
MODINFO.LacerteLead,
                grdb.teamlead AS grdblacertelead, MODINFO.LacerteResearcher,
                grdb.developer AS grdblacertedeveloper, MODINFO.EFLead,
MODINFO.EFDeveloper,
                MODINFO.NGILead, MODINFO.NGIDeveloper,
                to_char(AANDA.LsctoDev, 'MM/DD/YY') AS lsctodev,
AANDA.LscStatus,
                to_char(AANDA.LscSent, 'MM/DD/YY') AS lscsent,
                to_char(AANDA.LscApp, 'MM/DD/YY') AS lscapp,
                grdb.busunit || grdb.state AS newlacertemodequiv,
                ALLFORMS.OSfirstchk, MODINFO.Module, ALLFORMS.Scannablechk,
                CASE WHEN comchk = true THEN 'C'
                     ELSE ''
                     END AS CT,
                ALLFORMS.Inactive, aanda_status_types.code,
                modinfo.proseries_actual_date, modinfo.turbotax_actual_date,
                modinfo.webturbotax_actual_date,
modinfo.lacerte_actual_date,
                grdb.lacertefilename AS lacertename, grdb.lacerteformname
  FROM ((MODINFO INNER JOIN
       (ALLFORMS INNER JOIN AANDA ON ALLFORMS.File__no = AANDA.File__no)
       ON MODINFO.Module = ALLFORMS.Mod) LEFT JOIN aanda_status_types
       ON AANDA.aanda_status_type_id =
aanda_status_types.aanda_status_type_id)
       LEFT JOIN grdb ON allforms.file__no = grdb.intuitfilename
 WHERE ALLFORMS.Inactive Is Null;

Explain Analyze:
Unique  (cost=2870.62..3313.15 rows=340 width=527) (actual
time=9864.94..10209.69 rows=5721 loops=1)
  ->  Sort  (cost=2870.62..2870.62 rows=3404 width=527) (actual
time=9864.92..9938.36 rows=5721 loops=1)
        ->  Hash Join  (cost=801.42..2198.75 rows=3404 width=527) (actual
time=1475.11..7707.23 rows=5721 loops=1)
              ->  Hash Join  (cost=575.00..1146.02 rows=3404 width=452)
(actualtime=982.24..4017.73 rows=2983 loops=1)
                    ->  Hash Join  (cost=573.90..1127.80 rows=3404
width=443) (actual time=981.46..3333.14 rows=2983 loops=1)
                          ->  Hash Join  (cost=524.29..993.09 rows=3404
width=233) (actual time=952.03..2584.97 rows=2985 loops=1)
                                ->  Seq Scan on aanda  (cost=0.00..112.30
rows=3530 width=180) (actual time=0.23..322.69 rows=3530 loops=1)
                                ->  Hash  (cost=422.64..422.64 rows=8259
width=53) (actual time=910.08..910.08 rows=0 loops=1)
                                      ->  Seq Scan on allforms
(cost=0.00..422.64 rows=8259 width=53) (actual time=0.15..738.43 rows=8346
loops=1)
                          ->  Hash  (cost=48.69..48.69 rows=369 width=210)
(actual time=29.13..29.13 rows=0 loops=1)
                                ->  Seq Scan on modinfo  (cost=0.00..48.69
rows=369 width=210) (actual time=0.11..23.78 rows=369 loops=1)
                    ->  Hash  (cost=1.08..1.08 rows=8 width=9) (actual
time=0.42..0.42 rows=0 loops=1)
                          ->  Seq Scan on aanda_status_types
(cost=0.00..1.08 rows=8 width=9) (actual time=0.15..0.32 rows=8 loops=1)
              ->  Hash  (cost=145.14..145.14 rows=5314 width=75) (actual
time=439.53..439.53 rows=0 loops=1)
                    ->  Seq Scan on grdb  (cost=0.00..145.14 rows=5314
width=75) (actual time=0.09..292.42 rows=5314 loops=1)
Total runtime: 10273.98 msec

> Datatypes?  Please post \d tabelname for the tables involved.
I should warn you that I didn't design these tables, and cannot currently
change them.

goforms=> \d modinfo;
                              Table "modinfo"
         Column          |            Type             |     Modifiers
-------------------------+-----------------------------+-------------------
 module                  | character varying(4)        |
 lacertemodequiv         | character varying(4)        |
 forms_lead              | character varying(20)       |
 idgaandalead            | character varying(20)       |
 idgaandapoc             | character varying(20)       |
 team_leader             | character varying(20)       |
 developer               | character varying(20)       |
 taxqa                   | character varying(20)       |
 lacertelead             | character varying(20)       |
 lacerteresearcher       | character varying(20)       |
 lacerteqa               | character varying(20)       |
 eflead                  | character varying(20)       |
 efdeveloper             | character varying(20)       |
 ngilead                 | character varying(20)       |
 ngideveloper            | character varying(20)       |
 blank_second            | character varying(20)       |
 letter_of_intent_flag   | character varying(1)        |
 letterofintentchk       | smallint                    |
 loi_sent                | timestamp without time zone |
 guidelines_flag         | character varying(1)        |
 guidelineschk           | smallint                    |
 guidelines_recd         | timestamp without time zone |
 scannable_forms_flag    | character varying(1)        |
 scannableformschk       | smallint                    |
 dropout_ink_forms_flag  | character varying(1)        |
 dropoutinkformschk      | smallint                    |
 order_form_flag         | character varying(1)        |
 orderformchk            | smallint                    |
 order_form_recd         | timestamp without time zone |
 form_request_sent       | timestamp without time zone |
 id_code_reqd_flag       | character varying(1)        |
 idcodereqdchk           | smallint                    |
 id_code                 | character varying(30)       |
 fontandstyle            | character varying(30)       |
 previous_code_okay_flag | character varying(1)        |
 previouscodeokaychk     | smallint                    |
 penny_lines_reqd_flag   | character varying(1)        |
 pennylinesreqdchk       | smallint                    |
 decimals_okay_flag      | character varying(1)        |
 decimalsokaychk         | smallint                    |
 zerosreqdchk            | smallint                    |
 accept_dotm_flag        | character varying(1)        |
 accept_grafx_flag       | character varying(1)        |
 notes                   | text                        |
 state                   | character varying(15)       |
 faxondemand             | character varying(15)       |
 bbs                     | character varying(15)       |
 scnspecschk             | smallint                    |
 scngridchk              | smallint                    |
 scnscanbandchk          | smallint                    |
 scnchkdigchk            | smallint                    |
 scnvarfldinfo           | character varying(255)      |
 scndocidchk             | smallint                    |
 scndocidinfo            | character varying(24)       |
 scndocidspace           | character varying(24)       |
 scnbarcodechk           | smallint                    |
 scnbarcodeinfo          | character varying(24)       |
 scnbarcodespace         | character varying(24)       |
 scnpatchchk             | smallint                    |
 scnpatchinfo            | character varying(24)       |
 scnpatchspace           | character varying(24)       |
 scnscanlinechk          | smallint                    |
 scnscanlineinfo         | character varying(24)       |
 scnscanlinespace        | character varying(24)       |
 scnseconddbarcodechk    | smallint                    |
 scnseconddbarcodeinfo   | character varying(24)       |
 scnseconddbarcodespace  | character varying(24)       |
 efannualapply           | boolean                     | default 'f'::bool
 efloireqd               | boolean                     | default 'f'::bool
 efnewapplyforchange     | boolean                     | default 'f'::bool
 efloisent               | timestamp without time zone |
 ef_application_sent     | timestamp without time zone |
 effedeerecd             | timestamp without time zone |
 efsteerecd              | timestamp without time zone |
 efpasswordsrecd         | timestamp without time zone |
 efackpappsent           | timestamp without time zone |
 efackppassconfirmed     | timestamp without time zone |
 efackpcontact           | character varying(24)       |
 efirscenter             | character varying(24)       |
 eftransirscenter        | character varying(24)       |
 efdirectdebit           | boolean                     | default 'f'::bool
 efdirectdep             | boolean                     | default 'f'::bool
 efccardpay              | boolean                     | default 'f'::bool
 efzerodue               | boolean                     | default 'f'::bool
 efperpin                | boolean                     | default 'f'::bool
 efpropin                | boolean                     | default 'f'::bool
 efdirectfiling          | boolean                     | default 'f'::bool
 efreject                | boolean                     | default 'f'::bool
 eflscefsupport          | boolean                     | default 'f'::bool
 efnrsupport             | boolean                     | default 'f'::bool
 efpysupport             | boolean                     | default 'f'::bool
 efamendedsupport        | boolean                     | default 'f'::bool
 effedconsent            | boolean                     | default 'f'::bool
 efstconsent             | boolean                     | default 'f'::bool
 efpatsopen              | timestamp without time zone |
 efpatsclosed            | timestamp without time zone |
 efliveopen              | timestamp without time zone |
 efliveclosed            | timestamp without time zone |
 efnotes                 | text                        |
 effedapp                | boolean                     | default 'f'::bool
 efstateapp              | boolean                     | default 'f'::bool
 efefinetins             | boolean                     | default 'f'::bool
 efindependent           | boolean                     | default 'f'::bool
 efjelf                  | boolean                     | default 'f'::bool
 efackprovider           | character varying(10)       |
 efmbuseridtest          | timestamp without time zone |
 efmbuseridlive          | timestamp without time zone |
 efackpasstest           | timestamp without time zone |
 efackpasslive           | timestamp without time zone |
 efsoftwareidrecq        | boolean                     | default 'f'::bool
 efextensionsupport      | boolean                     | default 'f'::bool
 efforeignsupport        | boolean                     | default 'f'::bool
 proseries_actual_date   | timestamp without time zone |
 turbotax_actual_date    | timestamp without time zone |
 webturbotax_actual_date | timestamp without time zone |
 lacerte_actual_date     | timestamp without time zone |
Indexes: modinfo_module
Unique keys: modinfo_pk

goforms=> \d allforms
                         Table "allforms"
      Column       |           Type           |     Modifiers
-------------------+--------------------------+-------------------
 file__no          | character varying(8)     | not null
 mod               | character varying(4)     |
 frm_id            | character varying(3)     |
 inactive          | character varying(8)     |
 frm_wks           | character varying(18)    |
 pg_first          | character varying(2)     |
 pg_second         | character varying(2)     |
 filepgs           | smallint                 |
 totpgs            | character varying(2)     |
 title             | character varying(41)    |
 mainform          | boolean                  | default 'f'::bool
 scannablechk      | boolean                  | default 'f'::bool
 ddf_type          | character varying(3)     |
 seconddchk        | boolean                  | default 'f'::bool
 i                 | character varying(5)     |
 c                 | character varying(5)     |
 s                 | character varying(5)     |
 p                 | character varying(5)     |
 f                 | character varying(5)     |
 o                 | character varying(5)     |
 x                 | character varying(5)     |
 first             | character varying(5)     |
 second            | character varying(5)     |
 third             | character varying(5)     |
 fourth            | character varying(5)     |
 fifth             | character varying(5)     |
 starspecial       | character varying(5)     |
 dfd               | character varying(1)     |
 dog               | character varying(1)     |
 atb               | character varying(1)     |
 dst               | character varying(1)     |
 stfirstchk        | boolean                  | default 'f'::bool
 stsecondchk       | boolean                  | default 'f'::bool
 stthirdchk        | boolean                  | default 'f'::bool
 stfourthchk       | boolean                  | default 'f'::bool
 stfifthchk        | boolean                  | default 'f'::bool
 st6chk            | boolean                  | default 'f'::bool
 atg               | character varying(1)     |
 afd               | character varying(1)     |
 abf               | character varying(1)     |
 abs               | character varying(1)     |
 fedfirstchk       | boolean                  | default 'f'::bool
 frmsfirstchk      | boolean                  | default 'f'::bool
 frmssecondchk     | boolean                  | default 'f'::bool
 frmsthirdchk      | boolean                  | default 'f'::bool
 frmsfourthchk     | boolean                  | default 'f'::bool
 busfirstchk       | boolean                  | default 'f'::bool
 bussecondchk      | boolean                  | default 'f'::bool
 busthirdchk       | boolean                  | default 'f'::bool
 busfourthchk      | boolean                  | default 'f'::bool
 busfifthchk       | boolean                  | default 'f'::bool
 bus6chk           | boolean                  | default 'f'::bool
 osfirstchk        | boolean                  | default 'f'::bool
 ossecondchk       | boolean                  | default 'f'::bool
 osthirdchk        | boolean                  | default 'f'::bool
 osfourthchk       | boolean                  | default 'f'::bool
 osfifthchk        | boolean                  | default 'f'::bool
 os6chk            | boolean                  | default 'f'::bool
 ngifirstchk       | boolean                  | default 'f'::bool
 ngisecondchk      | boolean                  | default 'f'::bool
 effirstchk        | boolean                  | default 'f'::bool
 ofl               | character varying(1)     |
 aaa               | character varying(1)     |
 bbb               | character varying(1)     |
 fwchk             | boolean                  | default 'f'::bool
 ptchk             | boolean                  | default 'f'::bool
 aechk             | boolean                  | default 'f'::bool
 ltchk             | boolean                  | default 'f'::bool
 halchk            | boolean                  | default 'f'::bool
 comchk            | boolean                  | default 'f'::bool
 sim_to            | character varying(28)    |
 notes             | text                     |
 fedp              | character varying(1)     |
 fwbuild           | text                     |
 ptbuild           | text                     |
 ltbuild           | text                     |
 status            | character varying(20)    |
 lscapprovalstatus | character varying(20)    |
 change_time       | timestamp with time zone |
Indexes: allforms_file__no_idx,
         allforms_frm_wks_idx,
         allforms_mod_idx,
         allforms_title_idx
Primary key: allforms_pkey

goforms=> \d aanda
                             Table "aanda"
        Column        |            Type             |     Modifiers
----------------------+-----------------------------+-------------------
 file__no             | character varying(8)        |
 rev_date             | character varying(8)        |
 prelim_est           | timestamp without time zone |
 firstst_prelim       | timestamp without time zone |
 prelim_rec           | timestamp without time zone |
 final_est            | timestamp without time zone |
 firstst_final        | timestamp without time zone |
 final_rec            | timestamp without time zone |
 instprelimexp        | timestamp without time zone |
 firststinstprelim    | timestamp without time zone |
 instprelimrec        | timestamp without time zone |
 instrexp             | timestamp without time zone |
 firststinstr         | timestamp without time zone |
 instr_rec            | timestamp without time zone |
 m_w_lsr_to_dev       | timestamp without time zone |
 m_w_lsr_status       | character varying(2)        |
 m_w_inst_to_dev      | timestamp without time zone |
 m_w_inst_status      | character varying(2)        |
 lsr_sent             | timestamp without time zone |
 m_w_lsr_app          | timestamp without time zone |
 lsr_app_by           | character varying(20)       |
 lsr_writ_verb        | character varying(7)        |
 lsr_app_w_c          | character varying(1)        |
 lsr_resub_flag       | character varying(1)        |
 lsr_to_gwood         | timestamp without time zone |
 lsr_to_fsyst         | timestamp without time zone |
 gwood_recd           | timestamp without time zone |
 fsyst_recd           | timestamp without time zone |
 notes                | text                        |
 plus                 | character varying(2)        |
 lacerte              | boolean                     | default 'f'::bool
 re_submit            | character varying(1)        |
 lsctodev             | timestamp without time zone |
 lscstatus            | character varying(2)        |
 lscsent              | timestamp without time zone |
 lscapp               | timestamp without time zone |
 lscappby             | character varying(20)       |
 lscwrit_verb         | character varying(7)        |
 lscappw_c            | character varying(1)        |
 lscresub_flag        | character varying(1)        |
 lscre_submit_flag    | character varying(1)        |
 aanda_status_type_id | integer                     |
Indexes: aanda_file__no_idx,
         aanda_lateforms_idx1,
         aanda_lateforms_idx2,
         readytosend_idx
Unique keys: aanda_pk

goforms=> \d aanda_status_types
                                                  Table "aanda_status_types"
        Column        |         Type          |
  Modifiers
----------------------+-----------------------+-----------------------------
--------------------------------------------------
 aanda_status_type_id | integer               | not null default
nextval('aanda_status_types_aanda_status_type_id_Seq'::text)
 code                 | character varying(2)  |
 description          | character varying(50) |
Primary key: aanda_status_types_pkey

goforms=> \d grdb
                        Table "grdb"
     Column      |         Type          |     Modifiers
-----------------+-----------------------+-------------------
 lacertefilename | character varying(12) | not null
 lacerteformname | character varying(30) |
 state           | character varying(2)  |
 busunit         | character varying(1)  |
 formid          | character varying(4)  |
 intuitfilename  | character varying(12) |
 editthisfile    | boolean               | default 'f'::bool
 teamlead        | character varying(20) |
 developer       | character varying(20) |
Indexes: grdb_intuitfilename_idx
Primary key: grdb_pkey

goforms=> \d users
                       Table "users"
    Column     |         Type          |     Modifiers
---------------+-----------------------+-------------------
 name          | character varying(30) | not null
 e_mailaddress | character varying(50) |
 department    | character varying(24) |
 title         | character varying(30) |
 extension     | character varying(15) |
 password      | character varying(10) |
 active        | boolean               | default 'f'::bool
 getmail       | boolean               | default 'f'::bool
Primary key: users_pkey

Again if there is somewhere I can go to read about explain plans let me know
I'd like to figure it out as opposed to getting the answers handed to me.

Rob

On Mon, 8 Jul 2002 11:16:34 -0700, "Booth, Robert"
<Robert_Booth@intuit.com> wrote:
> [a lot of info I asked him to post]

Rob, I have to look a this closer first.  I'll be back ...

>Again if there is somewhere I can go to read about explain plans let me know
>I'd like to figure it out as opposed to getting the answers handed to me.

For now, here is what I found:

http://www.at.postgresql.org/users-lounge/docs/7.2/postgres/performance-tips.html
http://www.postgresql.org/idocs/index.php?performance-tips.html#USING-EXPLAIN

PostgreSQL: Introduction and Concepts by Bruce Momjian (online
version)
http://www.ca.postgresql.org/docs/aw_pgsql_book/node112.html
http://www.ca.postgresql.org/docs/aw_pgsql_book/node251.html

Practical PostgreSQL by John Worsley and Joshua Drake (online version)
http://www.commandprompt.com/ppbook/
http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=r26943%2ehtm

Servus
 Manfred

Manfred Koizar wrote:
> On Mon, 8 Jul 2002 11:16:34 -0700, "Booth, Robert"
> <Robert_Booth@intuit.com> wrote:
> > [a lot of info I asked him to post]
>
> Rob, I have to look a this closer first.  I'll be back ...
>
> >Again if there is somewhere I can go to read about explain plans let me know
> >I'd like to figure it out as opposed to getting the answers handed to me.
>
> For now, here is what I found:
>
> http://www.at.postgresql.org/users-lounge/docs/7.2/postgres/performance-tips.html
> http://www.postgresql.org/idocs/index.php?performance-tips.html#USING-EXPLAIN
>
> PostgreSQL: Introduction and Concepts by Bruce Momjian (online
> version)
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node112.html
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node251.html
>
> Practical PostgreSQL by John Worsley and Joshua Drake (online version)
> http://www.commandprompt.com/ppbook/
> http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=r26943%2ehtm

Also:

    http://developer.postgresql.org/pdf/internalpics.pdf

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Mon, 8 Jul 2002 11:16:34 -0700, "Booth, Robert"
<Robert_Booth@intuit.com> wrote:
>[about performance problems with a query containing two inner and
> eight outer joins]
Rob,

sorry for being quiet for so long, the printout of your mail got lost
in a pile of paper.

It wouldn`t have helped much, however, if I responded earlier.  I
could not find a hot spot in your query,  you are losing time
constantly,  not much with each outer join,  but it sums up.
Rewording your SQL won't help, AFAICS.

I thought of converting the outer joins to inner joins (you would have
to provide default entries in your lookup tables), writing the joins
in this form:
    FROM a, b, c ... WHERE a.b_id=b.id AND a.c_id=c.id ...
and letting the optimizer do its work, but I fear that you would again
end up with lots of hash joins.

Maybe caching the lookup tables in the frontend might help?

Servus
 Manfred