Обсуждение: 4 way JOIN using aliases
Hi All,
Thanks to all on the NOVICE list that gave me help I now have a query running
that returns the results I am after. :-) Now of course I want it to run
faster. Currently it clocks in at ~160ms. I have checked over the indexes
and I belive that the tables are indexed properly. The largest table,
tbl_item, only has 2000 rows. Is it possible to reduce the time of this query
further? I have included the output of EXPLAIN ANALYZE below the query.
Unfortunately I am still struggling trying to learn how to interpret the
output. TIA
SELECT tbl_item.id AS item_id,
tbl_item.item_type,
tbl_item.inactive AS item_inactive,
tbl_item.description AS item_description,
CASE WHEN tbl_item.item_class=0 THEN 'Non-Stock'
WHEN tbl_item.item_class=1 THEN 'Stock'
WHEN tbl_item.item_class=2 THEN 'Description'
WHEN tbl_item.item_class=3 THEN 'Assembly'
WHEN tbl_item.item_class=4 THEN 'Service'
WHEN tbl_item.item_class=5 THEN 'Labor'
WHEN tbl_item.item_class=6 THEN 'Activity'
WHEN tbl_item.item_class=7 THEN 'Charge'
ELSE 'Unrecognized'
END AS item_class,
tbl_item.sales_gl_account AS acct_sales_gl_nmbr,
sales_desc.description AS acct_sales_gl_name,
tbl_item.inventory_gl_account AS acct_inv_gl_nmbr,
inv_desc.description AS acct_inv_gl_name,
tbl_item.cogs_gl_account AS acct_cogs_gl_nmbr,
cogs_desc.description AS acct_cogs_gl_name,
CASE WHEN tbl_item.costing_method=0 THEN 'Average'
WHEN tbl_item.costing_method=1 THEN 'FIFO'
WHEN tbl_item.costing_method=2 THEN 'LIFO'
ELSE 'Unrecognized'
END AS acct_cost_method,
tbl_mesh.mesh_size,
tbl_mesh.unit_of_measure AS mesh_uom,
tbl_mesh.mesh_type,
tbl_item.purchase_description,
tbl_item.last_unit_cost AS purchase_unit_cost,
tbl_item.purchase_uom AS purchase_uom,
tbl_item.reorder_point AS purchase_point,
tbl_item.reorder_quantity AS purchase_quantity,
tbl_item.sales_description,
tbl_item.last_unit_cost/peachtree.tbl_item.ptos_uom_factor AS
sales_unit_cost,
tbl_item.unit_of_measure AS sales_uom,
tbl_item.weight AS sales_weight,
tbl_current.last_count
+ tbl_current.received
- tbl_current.shipped AS inv_on_hand,
tbl_current.allocated AS inv_committed,
tbl_current.last_count
+ tbl_current.received
- tbl_current.shipped
- tbl_current.allocated AS inv_available,
tbl_current.on_order AS inv_on_order
FROM tbl_item
LEFT JOIN tbl_mesh
ON ( tbl_item.id = tbl_mesh.item_id )
JOIN tbl_gl_account AS sales_desc
ON ( tbl_item.sales_gl_account = sales_desc.account_id )
JOIN tbl_gl_account AS inv_desc
ON ( tbl_item.inventory_gl_account = inv_desc.account_id )
JOIN tbl_gl_account AS cogs_desc
ON ( tbl_item.cogs_gl_account = cogs_desc.account_id )
LEFT JOIN tbl_current
ON ( tbl_item.id = tbl_current.item_id )
ORDER BY tbl_item.id;
Sort (cost=5749.75..5758.98 rows=3691 width=333) (actual
time=154.923..156.070 rows=1906 loops=1)
Sort Key: tbl_item.id
-> Hash Left Join (cost=2542.56..5194.32 rows=3691 width=333) (actual
time=30.475..146.074 rows=1906 loops=1)
Hash Cond: (("outer".id)::text = ("inner".item_id)::text)
-> Hash Join (cost=15.85..366.14 rows=3691 width=313) (actual
time=2.292..82.281 rows=1906 loops=1)
Hash Cond: (("outer".sales_gl_account)::text =
("inner".account_id)::text)
-> Hash Join (cost=11.18..305.81 rows=3749 width=290) (actual
time=1.632..61.052 rows=1906 loops=1)
Hash Cond: (("outer".cogs_gl_account)::text =
("inner".account_id)::text)
-> Hash Join (cost=6.50..244.60 rows=3808 width=267)
(actual time=1.034..40.873 rows=1906 loops=1)
Hash Cond: (("outer".inventory_gl_account)::text =
("inner".account_id)::text)
-> Hash Left Join (cost=1.82..182.50 rows=3868
width=244) (actual time=0.407..20.878 rows=1936 loops=1)
Hash Cond: (("outer".id)::text =
("inner".item_id)::text)
-> Seq Scan on tbl_item (cost=0.00..160.68
rows=3868 width=224) (actual time=0.131..5.022 rows=1936 loops=1)
-> Hash (cost=1.66..1.66 rows=66 width=34)
(actual time=0.236..0.236 rows=0 loops=1)
-> Seq Scan on tbl_mesh
(cost=0.00..1.66 rows=66 width=34) (actual time=0.031..0.149 rows=66 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32)
(actual time=0.573..0.573 rows=0 loops=1)
-> Seq Scan on tbl_gl_account inv_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.005..0.317 rows=214 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32) (actual
time=0.556..0.556 rows=0 loops=1)
-> Seq Scan on tbl_gl_account cogs_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.005..0.294 rows=214 loops=1)
-> Hash (cost=4.14..4.14 rows=214 width=32) (actual
time=0.603..0.603 rows=0 loops=1)
-> Seq Scan on tbl_gl_account sales_desc
(cost=0.00..4.14 rows=214 width=32) (actual time=0.031..0.343 rows=214 loops=1)
-> Hash (cost=1775.57..1775.57 rows=76457 width=31) (actual
time=26.114..26.114 rows=0 loops=1)
-> Seq Scan on tbl_current (cost=0.00..1775.57 rows=76457
width=31) (actual time=22.870..25.024 rows=605 loops=1)
Total runtime: 158.053 ms
Kind Regards,
Keith
Keith, > Thanks to all on the NOVICE list that gave me help I now have a query > running that returns the results I am after. :-) Now of course I want it > to run faster. Currently it clocks in at ~160ms. I have checked over the > indexes and I belive that the tables are indexed properly. The largest > table, tbl_item, only has 2000 rows. Is it possible to reduce the time of > this query further? Probably not, no. For a 7-way join including 2 LEFT JOINs on the unrestricted contents of all tables, 160ms is pretty darned good. If these tables were large, you'd be looking at a much longer estimation time. The only real way to speed it up would be to find a way to eliminate the left joins. Also, PostgreSQL 8.0 might optimize this query a little better. The only thing I can see to tweak is that the estimate on the number of rows in tbl_item is wrong; probably you need to ANALYZE tbl_item. But I doubt that will make a difference in execution time. -- Josh Berkus Aglio Database Solutions San Francisco
Keith Worthington wrote: > -> Seq Scan on tbl_current (cost=0.00..1775.57 rows=76457 > width=31) (actual time=22.870..25.024 rows=605 loops=1) This rowcount is way off -- have you run ANALYZE recently? -Neil
Neil Conway wrote: > Keith Worthington wrote: > >> -> Seq Scan on tbl_current (cost=0.00..1775.57 rows=76457 >> width=31) (actual time=22.870..25.024 rows=605 loops=1) > > > This rowcount is way off -- have you run ANALYZE recently? > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > Neil, I run vacuumdb with the analyze option every morning via a cron job. In my ignorance I do not know if that is the same thing. -- Kind Regards, Keith
> -----Original Message----- > From: Keith Worthington [mailto:KeithW@narrowpathinc.com] > Sent: Monday, April 11, 2005 7:44 PM > To: Neil Conway > Cc: PostgreSQL Perform > Subject: Re: [PERFORM] 4 way JOIN using aliases > > Neil Conway wrote: > > Keith Worthington wrote: > > > >> -> Seq Scan on tbl_current > (cost=0.00..1775.57 rows=76457 > >> width=31) (actual time=22.870..25.024 rows=605 loops=1) > > > > > > This rowcount is way off -- have you run ANALYZE recently? > > [...] > > I run vacuumdb with the analyze option every morning via a > cron job. In my ignorance I do not know if that is the same > thing. Pass it an --analyze option if you aren't already. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129
On Tue, 12 Apr 2005 08:41:55 -0500, Dave Held wrote
> > -----Original Message-----
> > From: Keith Worthington [mailto:KeithW@narrowpathinc.com]
> > Sent: Monday, April 11, 2005 7:44 PM
> > To: Neil Conway
> > Cc: PostgreSQL Perform
> > Subject: Re: [PERFORM] 4 way JOIN using aliases
> >
> > Neil Conway wrote:
> > > Keith Worthington wrote:
> > >
> > >> -> Seq Scan on tbl_current
> > (cost=0.00..1775.57 rows=76457
> > >> width=31) (actual time=22.870..25.024 rows=605 loops=1)
> > >
> > >
> > > This rowcount is way off -- have you run ANALYZE recently?
> > > [...]
> >
> > I run vacuumdb with the analyze option every morning via a
> > cron job. In my ignorance I do not know if that is the same
> > thing.
>
> Pass it an --analyze option if you aren't already.
>
> __
> David B. Held
>
Here is the command I have in the cron file.
vacuumdb --full --analyze --verbose --username dbuser --dbname ${IPA_DB} >>
${IPA_LOG_DIR}/ipavcmdb.log 2>&1
If this performs the analyze as I thought it should I do not know why the row
count is so badly off.
Kind Regards,
Keith
> -----Original Message-----
> From: Keith Worthington [mailto:keithw@narrowpathinc.com]
> Sent: Tuesday, April 12, 2005 1:14 PM
> To: Dave Held; PostgreSQL Perform
> Subject: Re: [PERFORM] 4 way JOIN using aliases
>
> > > I run vacuumdb with the analyze option every morning via a
> > > cron job. In my ignorance I do not know if that is the same
> > > thing.
> >
> > Pass it an --analyze option if you aren't already.
>
> Here is the command I have in the cron file.
>
> vacuumdb --full --analyze --verbose --username dbuser
> --dbname ${IPA_DB} >>
> ${IPA_LOG_DIR}/ipavcmdb.log 2>&1
>
> If this performs the analyze as I thought it should I do not
> know why the row
> count is so badly off.
You may need to increase the statistics target for the relevant
columns. Look at:
http://www.postgresql.org/docs/7.4/static/sql-altertable.html
In particular, the SET STATISTICS clause.
__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129