CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
pinug
pinug

Reputation: 211

add two simple indexes to optimize the query

I'm newbie in SQL table optimization so i have a simple/basic question; i have two SQL tables t1 and t2 below. If i had to add two simple indexes to optimize the query, which would should i choose ? Why ? (Please with details)

CREATE TABLE t1 (
    a int Primary Key,
    b int NOT NULL, 
    c char(10) references t2(e),
    d char(5)
);

CREATE TABLE t2 (
    e char(10) Primary Key
);

SELECT a
FROM t1 JOIN
     t2 
     ON c = e
WHERE abs(b)>50
ORDER BY d;

Upvotes: 0

Views: 51

Answers (2)

krokodilko
krokodilko

Reputation: 36107

CREATE TABLE t2 (
    e char(10) Primary Key
);

e colum is a primary key, so you don't need to create any index here because according to the documentation: the index is already there

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

For these two condition in the query:

     ON c = e
WHERE abs(b)>50

create an index on c column in table t1.

You may also try a multicolumn-functional index on ( c, abs(b) ), but the former should be enough. Hovewer if you decide to try it out, then remember that c column must be the first in the index because this column is used in = operator.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562280

You'll need an index on the expression abs(b) because a simple index on b won't help.

The index on t1 should also include d in hopes of helping the order by.

The index on t1 should also include a and c to make it an index-only scan.

The index on t2 should include e1 to help the join, and that should be satisfied by its primary key.

CREATE INDEX bk ON t1 (abs(b), d, a, c);

That's my attempt, but I am not a regular PostgreSQL user, and I haven't tested it.

Upvotes: 2

Related Questions