Comment by boulos | Hacker News Reader

Comment by boulos | Hacker News Reader

Huh. Like other commenters, I’m surprised to see a multi part JOIN be the answer for “I want the results of Query A as well as the results of Query B”.

If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. If not, WITH expressions to make the two independent queries and then a final SELECT to merge them however you prefer would be reasonable (whether that’s a join flavor or UNION/INTERSECT/etc.), but the super explosion JOIN just doesn’t cross my mind.

I think there must be a different example that they tried to anonymize for this post, otherwise they don’t need the IDs (and probably want a sum of quantity). If the author is around, what was the more realistic example behind:

“In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day. This requires a query that includes items that were both sold to customers as well as recorded as employee markouts for the specified store on the specified day.”?

Where this fail (and I don’t see the article addressing this concern) is if you want to do pagination. Say you are retrieving page 17 of 8000. Will the UNION still be performant then? After all, it will require a stable sort order that may depend on one of the branches. You certainly can’t pagination without doing a whole lot of logic in your application if you use two separate queries. My typical solution to this is to do a query not employee IDs, another one of customer IDs and then do an IN clause. Or you could do an intermediate ID of some kind that will collapse your diamond branch into a single IN.

In my experience this is the number one driver for infinite scroll, dropping paging simplifies a lot

Having infinite scroll doesn't avoid paging results from a query. Like the OP mentioned, if you have 8000 page * results/page records, you may only want to retrieve 50 records at a time to pass back to the client. In that case, you will still need a way to query for the next set / page. Infinite scroll is a UX implementation.

Yes - very true, however when we used paging we found users were going "I know my results are on page 19, skip to there" but when they were faced with the task of scrolling to page 19, their behaviour changed and they used filtering

Did that make the user experience worse or better?

Haha no comment

> "I know my results are on page 19, skip to there"

What on Earth is your application where that's the case? ..A forum maybe? (Revisiting a thread looking for a particular reply I mean.)

Document registry, but I've heard users doing this in just about every application that allows it - just doing a broad search and memorising the page that the thing they actually want is on

If users often search for known things then why not optimize the engine/UI to the use case instead of removing the use case?

Like, remembering the association <search term - selected results> and prominently display these?

Search results were boosted based on usage, but the performance benefits of infinite scroll outweighed the use case

Was about to say, either doc registry, or library catalog.

That's the neat thing about consistent search indexing that isn't being maliciously subverted or tweaked. Once a "good enough" structure is achieved, genearally someone can quickly find their way around. I call it the Librarian effect. I first grokked it's impact when I'd volunter to reshelve books in the library. Given a relatively ordered cart of books to put back on shelves, and an arbitrary starting point in the library to begin restacking (imagine going to the bathroom and some student comes and moves your cart on you), once you've done it a few times, you can usually unconsciously navigate the entire data store.

Compare this to most Search Engines, Reddit, or most Internet forums, where you have people maliciously attacking your overall index structure in a constant conflict for top page rank, orconstantly shifting page numbers based on time elapsed since last reply/engagement sorts.

Swear to God, might one day make a Dewey Decimal System-like search Engine. I've not encountered any other way of organizing information that seems intuitively mappable quite like it. Though I'll admit, I'm not sure what gives it the "stickyness" for me, the numbers, or the physical location. I'll preferentially choose a library whose physical layout I'm familiar with over one I don't, but if you take that physical layout and switch around all the numbers, I'll find you, and beat you with the heaviest reference volume in the Library once I find it.

Seriously though, even if you throw me in an unfamiliar layout, I'll generally quickly refresh on general identifiers then do a quick jog through the Library to nail down the layout. Haven't done many non-Dewey libraries, and hate the ones set up like bookstores where you don't have interleaved levels of sorting.

Have you ever used pornhub?

Yes it can - if you do it right. When you do paging, you need to count n·50 or whatever items

  LIMIT <page_size> OFFSET <page-number>*<page_size

With reasonable indexes, the OFFSET is the killer. When doing infinite scroll (or variable-size paging by predetermined items), you can ask for the next batch with a greater-than filter:

  SELECT ... where data > last_item ..
  LIMIT 50; -- no OFFSET

Note that you should probably use query-by-data even if performance is not a concern, as row addition/removal create artifacts in infinite scroll.

That being said, from user POV, I usually don't like infinite scroll - it can be time consuming, as you usually can't binary / guesstimate search the result set.

> If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”

That was my first reaction.

Keep in mind that high load applications have limited database connections. Running queries in parallel will be an anti-pattern in such applications.

Reply on news.ycombinator.com