Postgresql – way to optimize this using indexes

indexpostgresql

I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds.

SELECT
    C.yearID as year,
    name as teamName,
    C.lgID as league,
    D.cnt as totalBatters,
    C.cnt as aboveAverageBatters
FROM
    (SELECT 
        count(masterID) as cnt, A.yearID, A.teamID, A.lgID
    FROM
        (select 
        masterID,
            teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    GROUP BY teamID , yearID , lgID, masterid
    HAVING sum(AB) <> 0) B, (select 
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C,
    (SELECT 
        count(masterID) as cnt, yearID, teamID, lgID
    FROM
        batting
    GROUP BY yearID , teamID , lgID) D, 
    teams
WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

I've tried creating indexes for teamid, yearid, lgid, and ab, but performance is the same.

Please help!

Best Answer

Generally indexes will stop working when you run "top" select over a nested select, like:

(select 
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C

   (SELECT 
        count(masterID) as cnt, yearID, teamID, lgID
   FROM
        batting
   GROUP BY yearID , teamID , lgID) D

You define derived tables C (as well as D etc.). Also your derived tables have GROUP and FUNCTION inside.

When you make the comparison:

WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

it runs over new "tables" and these new tables do not have indexes.

QUESTION #1

Since I'll probably move to mysql cluster, does it make sense to switch from InnoDB to NDB engine and use HASH indexes on "category" and "status" columns?

ANSWER TO QUESTION #1

Hash indexes are for one-to-one lookups. Hash indexes are only available for the MEMORY Storage Engine (See my May 17, 2011 post Why does MySQL not have hash indices on MyISAM or InnoDB?)

QUESTION #2

I read how btrees stores data. If most of my queries involve a "WHERE category = x AND status = y", should I add 3 different indexes: one on category, one on status, and one on the combination of both?

ANSWER TO QUESTION #2

You do not want single column indexes. MySQL could still use them if there are no compound index present. It will make MySQL work harder generating results by merging lookups from two separate indexes,

You are way better off with a compound index, an index on both category and status.

In your particular case

the index could be (status,category) if you order categories within a status.
the index could be (category,status) if you order status values under a category (if the number of status values is high)
Since you do an ORDER BY within a (status,category) combination, your compound would benefit even further from one or both of these combined indexes
- (status,category,created_at)
- (category,status,created_at)
Please see my posts on using compound indexes
- Sep 18, 2012 : How are multiple indexes used in a query by MySQL? (Discusses INDEX_MERGE briefly)
- Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?

QUESTION #3

"show warnings" doesn't show anything useful about what mysql is trying to warn me about: what's wrong with my query?

ANSWER TO QUESTION #3

Without seeing the actual warning message, I can't tell you. Note this: You ran EXPLAIN twice on the same query and got two slightly different results. This was due to the choose of keys. InnoDB tends to take guess by passing through index pages inside the BTREE nodes of the indexes and chooses based on cardinality.

In your case, you should run just once

OPTIMIZE TABLE object;

This will defrag the table and generate a clean set of index statistics.

You could slightly improve the query by writing it as an INNER JOIN

select SQL_NO_CACHE count(*) from object
inner join offer on object.id = offer.id
where object.category = "bid"
and status = "pending"
order by created_at;

You should also think about the fact that offer no PRIMARY KEY.

You should run this query

SELECT COUNT(1),id,category from offer
GROUP BY id,category HAVING COUNT(1) > 1;

If you get no results back, then that can be your PRIMARY KEY. Thus, the offer table should be

CREATE TABLE `offer`
(
    id BIGINT UNSIGNED,
    `category` VARCHAR(31), /* genre, type, category */
    amount DECIMAL(12,3),
    PRIMARY KEY (id,category)
) ENGINE=InnoDB;

Postgresql – Postgres query plan has high rowcount, slow execution

(Unsure whether this belongs as an answer, comment or edit so I'll leave it here unless I hear otherwise.)

I was unable to find the cause of the bad row count. However, after rewriting the query like this,

SELECT t1."VisitorID", t1."Timestamp"
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT t2.VisitorID, t2.Timestamp
    FROM Table2 t2
    WHERE t2.SessionID = t1.SessionID
        AND t2.Timestamp > t1.Timestamp
)

the estimated row count went to a normal level and execution time decreased drastically, even on large datasets. I tested this with and without composite indexes like so:

CREATE INDEX "IX_Temp_Table1_Visitor-Session-Time"
ON "Table1"
USING BTREE ("VisitorID","SessionID","Timestamp");

CREATE INDEX "IX_Table2_Visitor-Session-Time"
ON "Table2"
USING BTREE ("VisitorID","SessionID","Timestamp");

and found no significant performance difference.