In-Reply-To: <200612311233.10201.rolfostvik@yahoo.no>
References: <200612311233.10201.rolfostvik@yahoo.no>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <5AB35188-5686-4201-A182-5A0321288084@fastcrypt.com>
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
Date: Sun, 31 Dec 2006 11:26:53 -0500
To: =?ISO-8859-1?Q?Rolf_=D8stvik?= <rolfostvik@yahoo.no>


On 31-Dec-06, at 6:33 AM, Rolf =D8stvik wrote:

>
> 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 =20
> 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. =20
> The original query
> contain 6 tables and was acceptable on 7.4.2, but took far too long =20=

> 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
> 7.4.14
> 	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
>
set effective_cache to 3G
shared buffers should be 1G on this computer for 8.2

Dave
> (It did however not have any influence of speed for
> 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
> -------------+---------+-----------
>  id          | integer | not null
>  uut_result  | integer |
>  step_parent | integer |
> 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
>
>              Table "public.uut_result_subset"
>      Column      |            Type             | Modifiers
> -----------------+-----------------------------+-----------
>  id              | integer                     | not null
>  start_date_time | timestamp without time zone |
> Indexes:
>     "uut_result_subset_pkey" PRIMARY KEY, btree (id)
>     "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
> 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
> 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 =20
> there are rows in table uut_result_subset.
> (In my data set this will be 176 500 rows, Other values for =20
> step_result_subset.step_parent is present 1003 times and lower.)
>
> Query: "select * from view_subset;" run against 7.4.14 server.
> QUERY PLAN
> ----------------------------------------------------------------------=20=

> --
>  Nested Loop  (cost=3D0.00..1400.86 rows=3D17 width=3D8) (actual =20
> time=3D0.161..26.287 rows=3D68 loops=3D1)
>    ->  Index Scan using uut_result_subset_start_date_time_idx on =20
> uut_result_subset ur  (cost=3D0.00..63.28 rows=3D18 width=3D4) (actual =
=20
> time=3D0.052..0.195 rows=3D68 loops=3D1)
>          Index Cond: (start_date_time > '2006-12-11 =20
> 00:00:00'::timestamp without time zone)
>    ->  Index Scan using step_result_uut_result_idx on =20
> step_result_subset sr  (cost=3D0.00..74.28 rows=3D2 width=3D8) (actual =
=20
> 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.
>
> QUERY PLAN
> ----------------------------------------------------------------------
>  Hash Join  (cost=3D339.61..77103.61 rows=3D96 width=3D8) (actual =20
> 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 =20
> step_result_subset sr  (cost=3D0.00..76047.23 rows=3D143163 width=3D8) =
=20
> (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 =20
> time=3D0.149..0.149 rows=3D68 loops=3D1)
>          ->  Bitmap Heap Scan on uut_result_subset ur  =20
> (cost=3D4.90..339.31 rows=3D118 width=3D4) (actual time=3D0.060..0.099 =
=20
> rows=3D68 loops=3D1)
>                Recheck Cond: (start_date_time > '2006-12-11 =20
> 00:00:00'::timestamp without time zone)
>                ->  Bitmap Index Scan on =20
> uut_result_subset_start_date_time_idx  (cost=3D0.00..4.90 rows=3D118 =20=

> width=3D0) (actual time=3D0.050..0.050 rows=3D68 loops=3D1)
>                      Index Cond: (start_date_time > '2006-12-11 =20
> 00:00:00'::timestamp without time zone)
>  Total runtime: 1010.775 ms
>
> Thanks for tips.
>
> Best regards
> Rolf =D8stvik
>
> ---------------------------(end of =20
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>