Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2

Rodrigo Rosenfeld Rosas <rr(dot)rosas(at)gmail(dot)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