From: Rafael Martinez <r.m.guerrero@usit.uio.no>
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
Date: Wed, 28 Aug 2013 11:07:05 +0200
Message-ID: <521DBDB9.6030107@usit.uio.no> (raw)
In-Reply-To: <521D19BD.70208@fuzzy.cz>
References: <521B4B2A.5010800@usit.uio.no>
<521C6F1F.5070901@usit.uio.no>
<521D19BD.70208@fuzzy.cz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
-----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
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-performance@postgresql.org
Cc: r.m.guerrero@usit.uio.no, tv@fuzzy.cz
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
In-Reply-To: <521DBDB9.6030107@usit.uio.no>
* 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