From: Dirk Lutzebaeck <lutzeb@aeccom.com>
MIME-Version: 1.0
Message-ID: <15635.36218.13754.670171@cayambe.core.aeccom.com>
Date: Fri, 21 Jun 2002 22:32:58 +0200
To: pgsql-sql@postgresql.org
Subject: Can somebody help me to optimize this huge query?
User-Agent: SEMI/1.13.7 (Awazu) CLIME/1.13.6 (=?ISO-2022-JP?B?GyRCQ2YbKEI=?=
	=?ISO-2022-JP?B?GyRCJU4+MRsoQg==?=) MULE XEmacs/21.1 (patch 14)
	(Cuyahoga Valley) (i386-redhat-linux)
Content-Type: text/plain; charset=US-ASCII


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