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
Import Notes
Resolved by subject fallback
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-EXPLAINPostgreSQL: 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.htmlPractical 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 19026On 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