Message-ID: <521DBDB9.6030107@usit.uio.no>
Date: Wed, 28 Aug 2013 11:07:05 +0200
From: Rafael Martinez <r.m.guerrero@usit.uio.no>
Organization: University of Oslo
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130806 Thunderbird/17.0.8
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>
CC: pgsql-performance@postgresql.org
Subject: Re: SQL statement over 500% slower with 9.2 compared with
 9.1
References: <521B4B2A.5010800@usit.uio.no> <521C6F1F.5070901@usit.uio.no>
 <521D19BD.70208@fuzzy.cz>
In-Reply-To: <521D19BD.70208@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/27/2013 11:27 PM, Tomas Vondra wrote:
[............]

> I don't immediately see where's the problem - maybe some other
> hacker on this list can. Can you prepare a testcase for this? I.e.
> a structure of the tables + data so that we can reproduce it?
> 

Hello

Of course, you can download a SQL dump of the tables involved, here:
http://folk.uio.no/rafael/filmdatabase_testcase.sql.gz

This file is 357M gunzipped and 101M gzipped. When restored in a
database it will use 1473MB.

# \d+
                          List of relations
 Schema |       Name        | Type  |  Owner   |  Size  | Description
- --------+-------------------+-------+----------+--------+-------------
 public | filmitem          | table | postgres | 41 MB  |
 public | filmparticipation | table | postgres | 540 MB |
 public | person            | table | postgres | 85 MB  |
(3 rows)

[dbpg-hotel-utv:5432/postgres@fdb_testcase][]# \di+
                                           List of relations
 Schema |              Name              | Type  |  Owner   |
Table       |  Size  | Description
-
--------+--------------------------------+-------+----------+-------------------+--------+-------------
 public | filmitempkey                   | index | postgres | filmitem
         | 26 MB  |
 public | filmparticipationfilmidindex   | index | postgres |
filmparticipation | 232 MB |
 public | filmparticipationpersonidindex | index | postgres |
filmparticipation | 232 MB |
 public | filmparticipationpkey          | index | postgres |
filmparticipation | 232 MB |
 public | personlastnameindex            | index | postgres | person
          | 41 MB  |
 public | personpkey                     | index | postgres | person
          | 37 MB  |

regards,
- -- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlIdvbkACgkQBhuKQurGihTZ0ACgk5ZpAvBFdhJs7A3xm3h80VhR
AX4AoIp+tSeeQtmmQh7ShP5WFI3hS+gp
=wK/M
-----END PGP SIGNATURE-----


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance