From: Rolf =?iso-8859-1?q?=D8stvik?= <rolfostvik@yahoo.no>
Organization: Ericsson AXXESSIT AS
To: pgsql-performance@postgresql.org
Date: Sun, 31 Dec 2006 12:33:09 +0100
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Disposition: inline
Subject: Worse perfomance on 8.2.0 than on 7.4.14
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200612311233.10201.rolfostvik@yahoo.no>


Hi

I have a simple query which uses 32ms on 7.4.14 and 1015ms on 8.2.0.
I guess 7.4.14 creates a better execution plan than 8.2.0 for this query but
i don't know how to get it to select a better one.
Explain analyse output will be found near the end of the e-mail.

(I have simplified my real query to get it as simple as possible. The origi=
nal query=20
contain 6 tables and was acceptable on 7.4.2, but took far too long on 8.1.=
4)

I have made a test setup to compare 7.4.14, 8.1.4 and 8.2.0.
8.1.4 and 8.2.0 uses the same execution plan and same time to execute.

postgresql.conf values i changed is=20
7.4.14=20
	Raised shared_buffers from 32MB to 128MB
	Raised temp_buffers from 8MB to 32MB
8.2.0
	Raised shared_buffers from 32MB to 128MB
	Raised temp_buffers from 8MB to 32MB
	Raised work_mem from 1MB to 8MB

(It did however not have any influence of speed for=20
the view_subset query shown below.)

vacuum analyze has been executed.

Computer:
	Dell PowerEdge 2950
	openSUSE Linux 10.1
	Intel(R) Xeon 3.00GHz
	4GB memory
	xfs filesystem on SAS disks

 Table "public.step_result_subset"
   Column    |  Type   | Modifiers=20
=2D------------+---------+-----------
 id          | integer | not null
 uut_result  | integer |=20
 step_parent | integer |=20
Indexes:
    "step_result_subset_pkey" PRIMARY KEY, btree (id)
    "step_result_subset_parent_key" btree (step_parent)
    "step_result_uut_result_idx" btree (uut_result)
Table contain 17 179 506 rows, and is ~400M when exported to file

=A0 =A0 =A0 =A0 =A0 =A0 =A0Table "public.uut_result_subset"
=A0 =A0 =A0Column =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0Type =A0 =A0 =A0 =A0 =
=A0 =A0 | Modifiers=20
=2D----------------+-----------------------------+-----------
=A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0| integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 | not null
=A0start_date_time | timestamp without time zone |=20
Indexes:
=A0 =A0 "uut_result_subset_pkey" PRIMARY KEY, btree (id)
=A0 =A0 "uut_result_subset_start_date_time_idx" btree (start_date_time)
Table contain ~176 555 rows, and is ~4.7M when exportd to file

Query is defined as view:

create view view_subset as=20
select
  ur.id as ur_id,
  sr.id as sr_id
from
  uut_result_subset as ur
   inner join step_result_subset as sr
    on ur.id=3Dsr.uut_result
where
  ur.start_date_time > '2006-12-11'
  and sr.step_parent=3D0;

Explain analyze is run several times to get a stable result=20
so i guess the numbers presented is with as much as possible
data in memory buffers.

Column step_result_subset.step_parent contain 0 in as many rows as there ar=
e rows in table uut_result_subset.
(In my data set this will be 176 500 rows, Other values for step_result_sub=
set.step_parent is present 1003 times and lower.)

Query: "select * from view_subset;" run against 7.4.14 server.
QUERY PLAN                                                                 =
            =20
=2D-----------------------------------------------------------------------
 Nested Loop  (cost=3D0.00..1400.86 rows=3D17 width=3D8) (actual time=3D0.1=
61..26.287 rows=3D68 loops=3D1)
   ->  Index Scan using uut_result_subset_start_date_time_idx on uut_result=
_subset ur  (cost=3D0.00..63.28 rows=3D18 width=3D4) (actual time=3D0.052..=
0.195 rows=3D68 loops=3D1)
         Index Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp wi=
thout time zone)
   ->  Index Scan using step_result_uut_result_idx on step_result_subset sr=
  (cost=3D0.00..74.28 rows=3D2 width=3D8) (actual time=3D0.149..0.379 rows=
=3D1 loops=3D68)
         Index Cond: ("outer".id =3D sr.uut_result)
         Filter: (step_parent =3D 0)
 Total runtime: 26.379 ms

Query: "select * from view_subset;" run against 8.4.0 server.
                                                                           =
    =20
QUERY PLAN                                                                 =
               =20
=2D---------------------------------------------------------------------
 Hash Join  (cost=3D339.61..77103.61 rows=3D96 width=3D8) (actual time=3D5.=
249..1010.669 rows=3D68 loops=3D1)
   Hash Cond: (sr.uut_result =3D ur.id)
   ->  Index Scan using step_result_subset_parent_key on step_result_subset=
 sr  (cost=3D0.00..76047.23 rows=3D143163 width=3D8) (actual time=3D0.082..=
905.326 rows=3D176449 loops=3D1)
         Index Cond: (step_parent =3D 0)
   ->  Hash  (cost=3D339.31..339.31 rows=3D118 width=3D4) (actual time=3D0.=
149..0.149 rows=3D68 loops=3D1)
         ->  Bitmap Heap Scan on uut_result_subset ur  (cost=3D4.90..339.31=
 rows=3D118 width=3D4) (actual time=3D0.060..0.099 rows=3D68 loops=3D1)
               Recheck Cond: (start_date_time > '2006-12-11 00:00:00'::time=
stamp without time zone)
               ->  Bitmap Index Scan on uut_result_subset_start_date_time_i=
dx  (cost=3D0.00..4.90 rows=3D118 width=3D0) (actual time=3D0.050..0.050 ro=
ws=3D68 loops=3D1)
                     Index Cond: (start_date_time > '2006-12-11 00:00:00'::=
timestamp without time zone)
 Total runtime: 1010.775 ms

Thanks for tips.

Best regards
Rolf =D8stvik