From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2
Date: Wed, 07 Nov 2012 19:58:02 -0500
Message-ID: <6034.1352336282@sss.pgh.pa.us> (raw)
In-Reply-To: <509AD4C4.5070003@gmail.com>
References: <509944DE.6020400@gmail.com>
<CAHyXU0ztB0e8eTidBbNXnhO6QjzaurXRGEBGbBt6P7oNY6vYcQ@mail.gmail.com>
<5099525B.5070909@gmail.com>
<CAHyXU0yKyz43s1GXO2QBDRwK8FeNqNAb6S0XUU66dSg+BD=xhg@mail.gmail.com>
<50995D7C.9020108@gmail.com>
<17866.1352229874@sss.pgh.pa.us>
<50996583.2080703@gmail.com>
<18371.1352231105@sss.pgh.pa.us>
<CAHyXU0xRk0LCj6NYuTAUaWN2FDAWj+jHgH2niDAGtb1w4QT83w@mail.gmail.com>
<509A42F9.6040403@gmail.com>
<CAHyXU0wn4vftY2Q4yFZNLu9XNeRRjTUnMXKw-BmmvixJMMvohw@mail.gmail.com>
<10569.1352307514@sss.pgh.pa.us>
<509AD4C4.5070003@gmail.com>
Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com> writes:
> Ok, I could finally strip part of my database schema that will allow you
> to run the explain query and reproduce the issue.
> There is a simple SQL dump in plain format that you can restore both on
> 9.1 and 9.2 and an example EXPLAIN query so that you can see the
> difference between both versions.
> Please keep me up to date with regards to any progress. Let me know if
> the commit above fixed this issue.
AFAICT, HEAD and 9.2 branch tip plan this query a bit faster than 9.1
does. It does appear that the problem is the same one fixed in that
recent commit: the problem is you've got N join clauses all involving
t.id and so there are lots of redundant ways to use the index on t.id.
I've got to say though that this is one of the most bizarre database
schemas I've ever seen. It seems to be sort of an unholy combination of
EAV and a star schema. A star schema might not actually be a bad model
for what you're trying to do, but what you want for that is one big fact
table and a collection of *small* detail tables you join to it (small
meaning just one entry per possible value). The way this is set up, you
need to join two or three tables before you can even join to the main
fact table - and those tables don't even have the virtue of being small.
That's never going to perform well.
regards, tom lane
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: tgl@sss.pgh.pa.us, rr.rosas@gmail.com, mmoncure@gmail.com
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2
In-Reply-To: <6034.1352336282@sss.pgh.pa.us>
* 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