30

Is it possible in PostgreSQL to place an index on an expression containing fields of multiple tables. So for example an index to speed up an query of the following form:

SELECT *, (table1.x + table2.x) AS z
FROM table1
INNER JOIN table2
ON table1.id = table2.id
ORDER BY z ASC
Improve this question

2 Answers 2

Reset to default
23

No it's not possible to have an index on many tables, also it really wouldn't guarantee speeding up anything since you won't always get an Index Only Scan. What you really want is a materialized view but pg doesn't have those either. You can try implementing it yourself using triggers like this or this.

Update

As noted by @petter. The materialized views were introduced in 9.3.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You can create an index on multiple columns in one table. You can't create an index on columns in two different tables. I'm pretty sure this is true for all current SQL dbms, not just PostgreSQL.
PostgreSQL supports Materialized Views as of version 9.3: postgresql.org/docs/9.3/static/sql-creatematerializedview.html
@petter 9.3 wasn't around when I wrote that. Updated anyways. Thanks.
3

No, that's not possible in any currently shipping SQL dbms. Oracle supports bitmap join indexes, but that might not be relevant. It's not clear to me whether you want an index on only the join columns of multiple tables, or whether you want an index on arbitrary columns of joined tables.

To determine the real source of performance problems, learn to read the output of PostgreSQL's EXPLAIN ANALYZE.

Improve this answer

7 Comments

Don't really have an performance issue. Most time is consumed by sorting on an expression and I hoped to be able to speed up the query even further using a table spanning index. The rest of the time is mostly consumed by index scans and hash joins and I don't think I can speed up those. My question was mainly because of curiosity since I couldn't find anything about it.
No, like Jakub Kania said, the workaround in PostgreSQL requires triggers. (Because PostgreSQL doesn't yet support materialized views.)
@EwoutKleinsmann well, if you'd really want to I guess there's also a chance to create a function(table1.id,table1.x) that would do a query on table2 and lie it's immutable so an index can be created on it. But it would be unreliable because it wouldn't update automaticaly, unreliable because it's not guaranteed it would be used and slow because there are no IOS on functions and so right rows would be choosen and then the function would be run again. So I guess if you'd select top 10 from 5 milion rows ordered by t1.x+t2.x it would be faster but a trigger could do the same much better.
Isn't the trigger more desirable & less complex than materialized views anyway for simple cases & performance reasons? Assuming the whole VIEW is materialized, it's a lot cheaper to just copy over an expression or value to a table with a trigger and then reference that. I'm sure there is a way to avoid certain aspects of materialization, but the straightforward approach of simply materializing is not cheap and will slow a lot of things down with duplication. Perhaps Oracle & MSSQL can use/inherit the underlying tables for verbatim table column references, but I'm not sure this is possible.
It's not the trigger. You need triggers for every action on every underlying table. That could be a lot of procedural code. Being able to replace a lot of procedural code with a single SQL statement is, I think, more desirable and less complex. Whether that's cheaper to execute is a quality-of-implementation issue. PostgreSQL 9.3 supports CREATE MATERIALIZED VIEW, and 9.4 should extend its usability.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.