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

Em 07-11-2012 22:58, Tom Lane escreveu:
> 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.

Great! What is the estimate for 9.2.2 release?

> 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.

And what is the reason why fast.sql performs much better than slow.sql?
Is it possible to optimize the planner so that both fast.sql and
slow.sql finish about the same time?

> I've got to say though that this is one of the most bizarre database
> schemas I've ever seen.

Merlin seems to share your opinion on that. I'd love to try a different
database design when I have a chance.

What would you guys suggest me for handling my application requirements?

The only reason it is bizarre is because I have no idea on how to
simplify much our database design using relational databases. And pstore
also doesn't sound like a reasonable option either for our requirements.

The only other option I can think of is stop splitting
transaction_condition in many tables (one for each data type). Then I'd
need to include all possible columns in transaction_condition and I'm
not sure if it would perform better and what would be the implications
with regards to the database size since most columns will be null for
each record. This also introduces another issue. I would need to create
a trigger to detect if the record is valid upon insertion to avoid
creating records with all columns set to NULL for instance. Currently
each separate table that store the values have not-null constraints
among others to prevent this kind of problem. Triggers are more
complicated to maintain, specially because we're used to using an ORM
(except for this particular case where I generate the SQL query manually
instead of using an ORM for this).

Also, we migrate the database using standalone_migrations:

https://github.com/thuss/standalone-migrations

If we change a single line in the trigger code it won't be easy to see
what line has changed in the commit that introduces the change because
we would have to create a separate migration to alter the trigger with
all code repeated.

> 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.

If I understand correctly, you're suggesting that I dropped
transaction_condition(id, transaction_id, type_id) and replaced
condition_boolean_value(id, condition_id, value) with
condition_boolean_value(id, transaction_id, type_id, value) and repeat
the same idea for the other tables.

Is that right? Would that perform much better? If you think so, I could
try this approach when I find some time. But I'd also need to
denormalize other related tables I didn't send in the schema dump. For
instance, the documents snippets have also a condition_id column. Each
field value (transaction_condition) can have multiple contract snippets
in a table called condition_document_excerpt(id, document_id,
condition_id, "position"). I'd need to remove condition_id from it and
append transaction_id and type_id just like the values tables. No big
deal if this would speed up our queries.

Am I missing something?