Strange behavior when doing where and order by query in postgres

Question

Background: A large table, 50M+, all column in query is indexed.

when I do a query like this:

select * from table where A=? order by id DESC limit 10;

In statement, A, id are both indexed.
Now confusing things happen:

the more rows where returned, the less time whole sql cost
the less rows where returned, the more time whole sql cost

I have a guess here: postgres do the order by first, and then where , so it cost more time to find 10 row in the orderd index when target rowset is small(like find 10 particular sand on beach); opposite， if target rowset is large, it's easy to find the first 10.

Is it right? Or there are some other reason for this?

Final question: How to optimize this situation?

Check the execution plan

user330315
– user330315

2021-07-28 05:31:22 +00:00
Commented Jul 28, 2021 at 5:31 — user330315
– user330315, Commented Jul 28, 2021 at 5:31

jjanes · Accepted Answer · 2021-07-28 06:10:03Z

2

It can either use the index on A to apply the selectivity, then sort on "id" and apply the limit. Or it can read them already in order using the index on "id", then filter out the ones that meet the A condition until it finds 10 of them. It will choose the one it thinks is faster, and sometimes it makes the wrong choice.

If you had a multi-column index, on (A,id) it could use that one index to do both things, get the selectivity on A and still fetch the already in order by "id", at the same time.

answered Jul 28, 2021 at 6:10

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

3 Comments

Lin Lee Over a year ago

Amazing! Works like a charm

Gordon Linoff Over a year ago

@jjanes . . . I'm just curious if you have a doc reference for reading the sorted data and then filtering.

jjanes Over a year ago

This talks about using indexes for providing order, but doesn't say anything specifically about filtering: postgresql.org/docs/current/indexes-ordering.html. The filter is 'documented' by a line in theEXPLAIN output. (But the ordering is not, you have to infer it from the lack of a separate sort node)

AnilGoyal · Accepted Answer · 2021-07-28 05:46:32Z

0

Do you know PGAdmin? With "explain verbose" before your statement, you can check how the query is executed (meaning the order of the operators). Usually first happens the filter and only afterwards the sorting...

edited Jul 28, 2021 at 5:46

AnilGoyal

26.3k4 gold badges34 silver badges50 bronze badges

answered Jul 28, 2021 at 5:23

matschy

114 bronze badges

Collectives™ on Stack Overflow

Strange behavior when doing where and order by query in postgres

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related