2

I am running the following query:

SELECT * FROM foo WHERE name = 'Bob' ORDER BY address DESC LIMIT 25 OFFSET 1

Because I have records in the table with name = 'Bob' the query time is fast on a table of 10M records (<.5 seconds)

However, if I search for name = 'Susan' the query takes over 45 seconds. I have no records in the table where name = 'Susan'.

I have an index on each of name and address. I've vacuumed the table, analyzed it and have even tried to re-write the query:

SELECT * FROM (SELECT * FROM foo WHERE name = 'Bob' ORDER BY address DESC) f LIMIT 25 OFFSET 1

and can't find any solution. I'm not really sure how to proceed. Please note this is different than this post as my slowness only happens when there are no records.

EDIT: If I take out the ORDER BY address then it runs quickly. Obviously, I need that there. I've tried re-writing it (with no success):

SELECT * FROM (SELECT * FROM foo WHERE name = 'Bob') f ORDER BY address DESC LIMIT 25 OFFSET 1
Community
  • 1
  • 1
  • "slow" when there are no **matching** rows in a big table probably means it is doing a table scan. Please provide the explain plan. Also suspect you have "simplfied" this for our consumption, but the details could be important. – Paul Maxwell Feb 06 '16 at 05:05

2 Answers2

1

Examine the execution plan to see which index is being used. In this case, the separate indexes for name and address are not enough. You should create a combined index of name, then address for this query.

Think of an index as a system maintained copy of certain columns, in a different order from the original. In this case, you want to first find matches by name, then tie-break on address, then take until you have enough or run out of name matches.

By making name first in the multi-column index, the index will be sorted by name first. Then address will serve as our tie-breaker.

Under the original indexes, if the address index is the one chosen then the query's speed will vary based on how quickly it can find matches.

The plan (in english) would be: Proceed through all of the rows which happen to already be sorted by address, discard any that do not match the name, keep going until we have enough.

So if you do not get 25 matches, you read the whole table!

With my proposed multi-column index, the plan (in English) would be: Proceed through all of the name matching rows which happen to already be sorted by address. Start with the first one and take them until you have enough. If you run out, stop.

Amy B
  • 108,202
  • 21
  • 135
  • 185
  • 1
    Also note: if you learn to read execution plans, you will be in the elite top 1% of sql programmers. The power is yours. – Amy B Feb 06 '16 at 05:05
0

Since the situation is that a query without the Order By is much faster than the one with the Order By clause; I'd make 2 queries:

-One without the order by, limit 1, to know if you have at least one record.

In the case you have at least one, it's safe to run the query with Order by.

-If there's no record, no need to run the second query.

Yes, it's not a solution, but it will let you deliver your project. Just ensure you create a ticket to handle the technical debt after delivery ;) otherwise your lead developer will set you on fire.

Then, to solve the real technical problem, it will be useful to know which indices you have created. Without these it will be very hard to give you a proper solution!

Nico Andrade
  • 880
  • 5
  • 16
  • Although you may infer from the question that 1 record would avoid the problem, I suspect that you'd need to confirm there's enough (26) matching records to avoid reading the whole table. – Amy B Feb 06 '16 at 05:04