Complete Intro to SQL & PostgreSQL<\/a> from Brian Holt will be perfect for you.<\/p>\n\n\n\nFiltering and sorting<\/h2>\n\n\n\n Let’s dive right in. Imagine you work for a book distribution company. You’re responsible for publishers and need to query info on them. There are approximately 250,000 different publishers, with a wide variance in the number of books published by each, which we’ll explore.<\/p>\n\n\n\n
Let’s start easy. You want to see the top 10 books, sorted alphabetically, for a single publisher.<\/p>\n\n\n
explain<\/span> analyze<\/span>\nselect<\/span> *\nfrom<\/span> books\nwhere<\/span> publisher = 157595<\/span>\norder<\/span> by<\/span> title\nlimit<\/span> 10<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nThis publisher is relatively small, with only 65 books in its catalog. Nonetheless, the query is slow to run, taking almost four seconds<\/strong>.<\/p>\n\n\n\nIf you followed the same steps from part 1 to create this same database, note that your ids will be different.<\/p>\n\n\n\nThis is hardly surprising; there are a lot of rows in our table, and finding the rows for that publisher takes a while, since Postgres has to scan the entire heap.<\/p>\n\n\n\n
So we add an index on, for now, just publisher.<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_publisher ON<\/span> books(publisher);\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nWe can think of our index in this way. It just helps us identify all the book entries by publisher. To get the rest of the info on the book, we go to the heap.<\/p>\n\n\n\nAnd now our same query is incredibly fast.<\/p>\n\n\n\nNothing surprising or interesting.<\/p>\n\n\n\n
But now you need to run the same query, but on a different publisher, number 210537. This is the biggest publisher in the entire database, with over 2 million books. Let’s see how our index fares.<\/p>\n\n\n
explain<\/span> analyze<\/span>\nselect<\/span> *\nfrom<\/span> books\nwhere<\/span> publisher = 210537<\/span>\norder<\/span> by<\/span> title\nlimit<\/span> 10<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nActually, our index wasn’t used at all. Postgres just scanned the whole table, grabbing our publisher along the way, and then sorted the results to get the top 10. We’ll discuss why a little later, as we did in the prior post, but the short of it is that the random<\/strong> heap accesses from reading so many entries off of an index would be expensive; Postgres decided the scan would be cheaper. These decisions are all about tradeoffs and are governed by statistics and cost estimates.<\/p>\n\n\n\nPreviously, we threw the “other” field into the INCLUDE()<\/code> list, so the engine wouldn’t have to leave the index to get the other field it needed. In this case, we’re selecting everything<\/em>. I said previously to be diligent in avoiding unnecessary columns in the SELECT<\/code> clause for just this reason, but here, we assume we actually do need all these columns.<\/p>\n\n\n\nWe probably don’t want to dump every single column into the INCLUDE<\/code> list of the index: we’d basically just be redefining our table into an index.<\/p>\n\n\n\nBut why do we need to read so many rows in the first place? We have a limit of 10 on our query. The problem, of course, is that we’re ordering on title. And Postgres needs to see all rows for a publisher (2 million rows in this case) in order to sort them, and grab the first 10.<\/p>\n\n\n\n
What if we built an index on publisher<\/code>, and then<\/em> title<\/code>?<\/p>\n\n\nCREATE<\/span> INDEX<\/span> idx_publisher_title ON<\/span> books(publisher, title);\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nThat would look like this:<\/p>\n\n\n\nIf Postgres were to search for a specific publisher, it could just seek down to the start of that publisher’s books, and then read however many needed, right off the leaf nodes, couldn’t it? There could be 2 million book entries in the leaf nodes, but Postgres could just read the first 10, and be guaranteed that they’re the first 10, since that’s how the index is ordered.<\/p>\n\n\n\n
Let’s try it.<\/p>\n\n\n\nWe got the top 10 books, sorted, from a list of over two million in less than a fourth of a millisecond. Amazing!<\/p>\n\n\n\n
More publishers!<\/h2>\n\n\n\n Now your boss comes and tells you to query the top 10 books, sorted alphabetically, as before, but over either<\/strong> publisher, combined. To be clear, the requirement is to take all books both publishers have published, combine them, then get the first ten, alphabetically.<\/p>\n\n\n\nEasy, you say assuredly, fresh off the high of seeing Postgres grab you that same data for your enormous publisher in under a millisecond.<\/p>\n\n\n\n
You can put both publisher ids into an IN<\/code> clause. Then, Postgres can search for each, one at a time, save the starting points of both, and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.<\/p>\n\n\n\nLet’s try it!<\/p>\n\n\n
explain<\/span> analyze<\/span>\nselect<\/span> *\nfrom<\/span> books\nwhere<\/span> publisher in<\/span> (157595<\/span>, 210537<\/span>)\norder<\/span> by<\/span> title\nlimit<\/span> 10<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nWhich produces this<\/p>\n\n\n\n[Sad Trombone]<\/em><\/p>\n\n\n\nLet’s re-read my completely made-up, assumed chain of events Postgres would take, from above.<\/p>\n\n\n\n
\nPostgres can search for each, one at a time, save the starting points of both, and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.<\/p>\n<\/blockquote>\n\n\n\n
It reads like the Charlie meme from Always Sunny in Philadelphia.<\/p>\n\n\n
\n
If your description of what the database will do sounds like something that would fit with this meme, you’re probably overthinking things.<\/p>\n\n\n\n
Postgres operates on very simple operations that it chains together. Index Scan, Gather Merge, Sort, Sequential Scan, etc.<\/p>\n\n\n\n
Searching multiple publishers<\/h2>\n\n\n\n To be crystal clear, Postgres absolutely can search multiple keys from an index. Here’s the execution plan for the identical query from a moment ago, but with two small publishers for the publisher ids, which each have just a few hundred books<\/p>\n\n\n\nIt did indeed do an index scan, on that same index. It just matched two values at once.<\/p>\n\n\n\n
Rather than taking one path down the B Tree, it takes multiple paths down the B Tree, based on the multiple key value matches.<\/p>\n\n\n\n
Index Cond: (publisher = ANY ('{157595,141129}'::integer[]))\n<\/pre>\n\n\n\nThat gives us all<\/strong> rows for either<\/em> publisher. Then it needs to sort them, which it does next, followed by the limit.<\/p>\n\n\n\nWhy does it need to sort them? When we have a single<\/em> publisher, we know<\/em> all values under that publisher are ordered.<\/p>\n\n\n\nLook<\/em> at the index.<\/p>\n\n\n\nImagine we searched for publisher 8. Postgres can go directly to the beginning of that publisher, and just read<\/em>:<\/p>\n\n\n\n\"Animal Farm\"Look what happens when we search for two<\/em> publishers, 8 and also, now, 21.<\/p>\n\n\n\nWe can’t just start reading for those matched records. That would give us<\/p>\n\n\n\n
\"Animal Farm\"\n\"Of Mice and Men\"\n\"Lord of The Flies\"\n\"The Catcher in The Rye\"\n<\/pre>\n\n\n\nThe books under each publisher are ordered, but the overall list of matches is not. And again, Postgres operates on simple<\/em> operations. Elaborate meta descriptions like “well it’ll just merge the matches from each publisher taking the less of the next entry from either until the limit is satisfied” won’t show up in your execution plan, at least not directly.<\/p>\n\n\n\nWhy did the publisher id change the plan?<\/h3>\n\n\n\n Before we make this query fast, let’s briefly consider why our query’s plan changed so radically between searching for two small publishers compared to an enormous publisher and a small one.<\/p>\n\n\n\n
As we discussed in part 1, Postgres tracks and uses statistics about your data in order to craft the best execution plan it can. Here, when you searched for the large publisher, it realized that query would yield an enormous number of rows. That led it to decide that simply scanning through the heap directly would be faster than the large number of random i\/o that would be incurred from following so many matches in the index’s leaf nodes, over to the corresponding locations on the heap. Random i\/o is bad, and Postgres will usually try to avoid it.<\/p>\n\n\n\n
Crafting a better query<\/h2>\n\n\n\n You can absolutely have Postgres find the top 10 books in both publishers, and then put them together, sorted, and take the first 10 from there. You just have to be explicit about it.<\/p>\n\n\n
explain<\/span> analyze<\/span>\nwith<\/span> pub1 as<\/span> (\n select<\/span> * from<\/span> books\n where<\/span> publisher = 157595<\/span>\n order<\/span> by<\/span> title limit<\/span> 10<\/span>\n), pub2 as<\/span> (\n select<\/span> * from<\/span> books\n where<\/span> publisher = 210537<\/span>\n order<\/span> by<\/span> title limit<\/span> 10<\/span>\n)\nselect<\/span> * from<\/span> pub1\nunion<\/span> all<\/span>\nselect<\/span> * from<\/span> pub2\norder<\/span> by<\/span> title\nlimit<\/span> 10<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nThe syntax below is called a common table expression, or a CTE. It’s basically a query that we define, and then query against later.<\/p>\n\n\n
with<\/span> pub1 as<\/span> (\n select<\/span> * from<\/span> books\n where<\/span> publisher = 157595<\/span>\n order<\/span> by<\/span> title limit<\/span> 10<\/span>\n)\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\nLet’s run it!<\/p>\n\n\n\n
The execution plan is beautiful<\/p>\n\n\n\nIt’s fast! As you can see, it runs in less than a fifth of a millisecond (0.186ms \u2014 but who’s counting)?<\/p>\n\n\n\n
Always read these from the bottom:<\/p>\n\n\n\nIt’s the same exact index scan from before, but on a single publisher, with a limit of 10, run twice. Postgres can seek to the right publisher, and just read 10 for the first publisher, and then repeat for the second publisher. Then it puts those lists together.<\/p>\n\n\n\n
Remember the silly, contrived Postgres operation I made up before?<\/p>\n\n\n\n
\n… and then start reading forward on both, and sort of merge them together, taking the smaller title from either, until you have 10 books total.<\/p>\n<\/blockquote>\n\n\n\n
You’re not going to believe this, but that’s exactly what the Merge Append on line 2 does<\/p>\n\n\n\n
-> Merge Append (cost=1.40..74.28 rows=20 width=111) (actual time=0.086..0.115 rows=10 loops=1)\n<\/pre>\n\n\n\nYou can achieve amazing things with modern databases if you know how to structure your queries just<\/em> right.<\/p>\n\n\n\nHow does this scale?<\/h2>\n\n\n\n You won’t want to write queries like this manually. Presumably, you’d have application code taking a list of publisher ids, and constructing something like this. How will it perform as you add more and more publishers?<\/p>\n\n\n\n
I’ve explored this very idea on larger production sets of data (much larger than what we’re using here). I found that, somewhere around a thousand<\/em> ids, the performance does break down. But not because there’s too much data to work with. The execution<\/em> of those queries, with even a thousand ids, took only a few hundred ms<\/code>. But the Planning Time<\/em> started to take many, many seconds. It turns out having Postgres parse through a thousand CTEs, and put a plan together takes time.<\/p>\n\n\n\nVersion 2<\/h2>\n\n\n\n We’re onto something, for sure. But can we take a list of ids, and force them into individual queries that match on that specific id, with a limit, and then select from the overall bucket of results? Exactly like before, but without having to manually cobble together a CTE for each id?<\/p>\n\n\n\n
When there’s a will, there’s a way.<\/p>\n\n\n
explain<\/span> analyze<\/span>\nwith<\/span> ids as<\/span> (\n select<\/span> * from<\/span> (\n values<\/span> (157595<\/span>), (210537<\/span>)\n ) t(id)\n), results as<\/span> (\n select<\/span> bookInfo.*\n from<\/span> ids\n cross<\/span> join<\/span> lateral<\/span> (\n select<\/span> *\n from<\/span> books\n where<\/span> publisher = ids.id\n order<\/span> by<\/span> title\n limit<\/span> 10<\/span>\n ) bookInfo\n)\nselect<\/span> *\nfrom<\/span> results\norder<\/span> by<\/span> title\nlimit<\/span>
![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img1-basic-publisher-query.png?resize=1024%2C462&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img2-publisher-btree.png?resize=1024%2C294&ssl=1\")
<\/figure>\n\n\n\n![\"Execution](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img3-small-publisher-query-plan.png?resize=1024%2C225&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img4-large-publisher-plan.png?resize=1024%2C449&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img5-publisher-title-index.png?resize=1024%2C347&ssl=1\")
<\/figure>\n\n\n\n![\"Execution](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img6-fast-search-large-pub.png?resize=1024%2C150&ssl=1\")
<\/figure>\n\n\n\n![\"Query](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img7-multiple-publishers.png?resize=1024%2C447&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/always-sunny-meme.gif?resize=640%2C404&ssl=1\")
<\/figure>\n<\/div>\n\n\n![\"Query](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img8-multiple-small-publishers.png?resize=1024%2C215&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img9-single-publisher-read.png?resize=1024%2C352&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img10-double-publisher-read.png?resize=1024%2C348&ssl=1\")
<\/figure>\n\n\n\n![\"A](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img11-cte-append.png?resize=1024%2C257&ssl=1\")
<\/figure>\n\n\n\n![\"Execution](\"https:\/\/i0.wp.com\/frontendmasters.com\/blog\/wp-content\/uploads\/2025\/08\/img11a-cte-append.png?resize=1024%2C130&ssl=1\")
<\/figure>\n\n\n\n