72423

PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index

I have a table that contains an 'id' column of type BIGSERIAL. I also have an index for this one column (sort order descending, BTREE, unique).

I often need to retrieve the last 10, 20, 30 entries from a table of millions of entries, like this:

SELECT * FROM table ORDER BY id DESC LIMIT 10

I would have thought it's a pretty clear case: there's an index for this particular field, sort order matches, and I need only 10 entries compared to millions in the whole table, this query definitely uses an index scan.

But it doesn't it does a sequential scan over the whole table.

I try to dig deeper, didn't find anything unusual. The Postgres doc at https://www.postgresql.org/docs/9.6/static/indexes-ordering.html says:

An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all.

But it still doesn't work. Does anybody have any pointers for me? Maybe I'm just not seeing the forrest for the trees anymore... :-(

Answer1:

Ok, saying it out loud and trying to gather more information to put into my question apparently made me see the forrest again, I found the actual problem. Further down in the doc I mentioned above is this sentence:

An index stored in ascending order with nulls first can satisfy either ORDER BY x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on which direction it is scanned in.

This was the problem. I specified the sort order in the index but I ignored the NULLS FIRST vs. LAST.

Postgres default is NULLS FIRST if you don't mention it explicitly in your query. So what Postgres found was the combination ORDER BY DESC NULLS FIRST which wasn't covered by my index. The combination of both SORT ORDER and NULLS is what matters.

The 2 possible solutions:

    <li>Either mention NULLS FIRST/LAST accordingly in the query so that it matches the index</li> <li>...or change the index to NULLS FIRST (which is what I did)</li> </ul>

    Now Postgres is doing a proper index scan and only touches 10 elements during the query, not all of them.

    Answer2:

    If you need to get last 10 entries in table you can use this:

    SELECT * FROM table WHERE id >= (SELECT MAX(id) FROM table) - 10 ORDER BY id DESC

    And similarly for 20 and 30 entries. This looks not so clear, but works fast as long as you have index for 'id' column.

Recommend

  • Set MySQL Column Alias to a Calculated Date
  • Incorrect output sankey diagram when using shiny
  • getting an Int/short/byte structure byte representation with C#
  • Border Layout not working
  • javascript use var value as name for new var
  • What is the best way to join ordered arrays?
  • Multiprocessing pool example (parallel) is slower than sequential. Trying to understand pool in pyth
  • Automating table/object name scan and search in SAS
  • PostgreSQL 9.1 timezones
  • docker-compose: connection refused between containers, but service accessible from host
  • Django Migrations fail during django initialization
  • PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea
  • Does the MySQL IN clause execute the subquery multiple times?
  • Motorola barcode scanner SDK events C#
  • How to skip require in ruby?
  • HttpListener.IsSupported is false on XP SP3
  • Spring: No transaction manager has been configured
  • Filter strings with regex before casting to numeric
  • JPA flush vs commit
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • ActiveRecord query for a count of new users by day
  • How to attach a node.js readable stream to a Sendgrid email?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • Optimizing database types to compact database (SQLite)
  • RectangularRangeIndicator format like triangular using dojo
  • Cross-Platform Protobuf Serialization
  • JSON with duplicate key names losing information when parsed
  • Display Images one by one with next and previous functionality
  • Large data - storage and query
  • Do I've to free mysql result after storing it?
  • Revoking OAuth Access Token Results in 404 Not Found
  • SQL merge duplicate rows and join values that are different
  • Turn off referential integrity in Derby? is it possible?
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?
  • How to Embed XSL into XML