From: Dirk Lutzebaeck <lutzeb@aeccom.com>
To: pgsql-sql@postgresql.org
Subject: Can somebody help me to optimize this huge query?
Date: Fri, 21 Jun 2002 22:32:58 +0200
Message-ID: <15635.36218.13754.670171@cayambe.core.aeccom.com> (raw)
Hi,
here is a query on two tables whith lots of self joins which just
takes hours to complete on 7.2.1. I use multi dimensional
indices which are shown in the explain comments. My question is how
can I use explicit join syntax to let the planner do better. I
think Geoq does not match yet because there are only 2 tables. The
schema behind models abtract document objects.
Here is the query:
SELECT DISTINCT
t_sek.docindex,
t_sek.envelope,
bt.oid,
bt.time
FROM
boxinfo bt, boxinfo bd, boxinfo bo,
docobj t_sek, docobj t_pgr, docobj t_sta, docobj t_sol,
docobj d_pnr, docobj d_sta,
docobj o_sek, docobj o_pgr, docobj o_pnr
WHERE
t_sek.docspec=124999684 and
t_pgr.docspec=124999684 and
t_sol.docspec=124999684 and
t_sta.docspec=124999684 and
d_pnr.docspec=15378692 and
d_sta.docspec=15378692 and
o_sek.docspec=125075754 and
o_pgr.docspec=125075754 and
o_pnr.docspec=125075754 and
bt.community=15042052 and
bd.community=15042052 and
bo.community=15042052 and
bt.member=111459733 and
bd.member=111459733 and
bo.member=111459733 and
bt.hide=FALSE and
bd.hide=FALSE and
bo.hide=FALSE and
o_sek.attrid=1 and o_pgr.attrid=4 and
t_sek.attrid=0 and t_pgr.attrid=2 and
t_sta.attrid=9 and t_sol.attrid=4 and
d_pnr.attrid=6 and d_sta.attrid=16 and
abstime(bd.time)::date > t_sol.val_date and
t_sol.val_date <= now()::date and
o_sek.val_str=t_sek.val_str and
o_pgr.val_str=t_pgr.val_str and
o_pnr.val_str=d_pnr.val_str and
t_sta.val_str=d_sta.val_str and
o_sek.envelope=o_pgr.envelope and
o_sek.envelope=o_pnr.envelope and
o_sek.docindex=o_pgr.docindex and
o_sek.docindex=o_pnr.docindex and
t_sek.envelope=t_pgr.envelope and
t_sek.envelope=t_sta.envelope and
t_sek.envelope=t_sol.envelope and
t_sek.docindex=t_pgr.docindex and
t_sek.docindex=t_sta.docindex and
t_sek.docindex=t_sol.docindex and
d_pnr.envelope=d_sta.envelope and
d_pnr.docindex=d_sta.docindex and
bt.envelope=t_sek.envelope and
bd.envelope=d_pnr.envelope and
bo.envelope=o_sek.envelope
Here is what explain says:
Unique (cost=3395.39..3395.40 rows=1 width=212)
-> Sort (cost=3395.39..3395.39 rows=1 width=212)
-> Nested Loop (cost=0.00..3395.38 rows=1 width=212)
-> Nested Loop (cost=0.00..3389.37 rows=1 width=190)
-> Nested Loop (cost=0.00..3383.35 rows=1 width=168)
-> Nested Loop (cost=0.00..3369.99 rows=1 width=146)
-> Nested Loop (cost=0.00..3363.98 rows=1 width=124)
-> Nested Loop (cost=0.00..3149.05 rows=36 width=102)
-> Nested Loop (cost=0.00..2727.76 rows=1 width=94)
-> Nested Loop (cost=0.00..2719.21 rows=1 width=82)
-> Nested Loop (cost=0.00..1813.58 rows=107 width=60)
-> Nested Loop (cost=0.00..1392.83 rows=1 width=48)
-> Nested Loop (cost=0.00..1325.31 rows=11 width=26)
-> Index Scan using boxinfo_j_index on boxinfo bo (cost=0.00..419.68 rows=107 width=4)
-> Index Scan using docobj_j_index on docobj o_sek (cost=0.00..8.44 rows=1 width=22)
-> Index Scan using docobj_j_index on docobj o_pgr (cost=0.00..6.00 rows=1 width=22)
-> Index Scan using boxinfo_j_index on boxinfo bt (cost=0.00..419.68 rows=107 width=12)
-> Index Scan using docobj_j_index on docobj t_sta (cost=0.00..8.44 rows=1 width=22)
-> Index Scan using docobj_j_index on docobj t_sol (cost=0.00..6.01 rows=1 width=12)
-> Index Scan using boxinfo_j_index on boxinfo bd (cost=0.00..419.68 rows=107
width=8)
-> Index Scan using docobj_j_index on docobj t_pgr (cost=0.00..6.00 rows=1 width=22) -> Index Scan using docobj_j_index on docobj o_pnr (cost=0.00..5.99 rows=1 width=22)
-> Index Scan using docobj_env_index on docobj d_pnr (cost=0.00..13.34 rows=2 width=22)
-> Index Scan using docobj_j_index on docobj t_sek (cost=0.00..6.00 rows=1 width=22)
-> Index Scan using docobj_j_index on docobj d_sta (cost=0.00..6.00 rows=1 width=22)
Maybe there are just too many joins :/
Dirk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lutzeb@aeccom.com
Subject: Re: Can somebody help me to optimize this huge query?
In-Reply-To: <15635.36218.13754.670171@cayambe.core.aeccom.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox