A SQL Heuristic: ORs Are Expensive
ethanseal.com
A SQL Heuristic: ORs Are Expensive
1–10 of 82 posts
Re: A SQL Heuristic: ORs Are Expensive
#2[deleted]
Re: A SQL Heuristic: ORs Are Expensive
#3I really like the extension pattern. I wish more of the tables at my company used it.
Another huge benefit that we're realizing as we move some of our heaviest tables to this pattern is that it makes it really easy to index a core set of fields in Elasticsearch, along with a tag to associate it with a particular domain model. This has drastically cut down on our search-specific denormlization and let us avoid expensive index schema updates.
Re: A SQL Heuristic: ORs Are Expensive
#4This sort of thing is why looking at generated SQL while developing instead of just trusting the ORM to write good queries is so important.
I find query planning (and databases in general) to be very difficult to reason about, basically magic. Does anyone have some recommended reading or advice?
Re: A SQL Heuristic: ORs Are Expensive
#5Can we agree that this is only applies to queries where all the filter conditions use cols with indexes? If no indexes can be used, a single full table scan with OR surely is faster than multiple full table scans.
Re: A SQL Heuristic: ORs Are Expensive
#6This sort of thing is why looking at generated SQL while developing instead of just trusting the ORM to write good queries is so important. I find query planning (and databases in general) to be very difficult to reason about, basically magic. Does anyone have some recommended reading or advice?
https://pages.cs.wisc.edu/~dbbook/ is a great overview, specifically chapters 13 and 14 on query optimization. it is difficult to reason about though, and every compiler is different. it takes time and enough examples to look at a query and have an intuition for what the plan should look like, and if there's something the compiler is not handling well.
Re: A SQL Heuristic: ORs Are Expensive
#7Can we agree that this is only applies to queries where all the filter conditions use cols with indexes? If no indexes can be used, a single full table scan with OR surely is faster than multiple full table scans.
Absolutely. Though I don't recall seeing multiple sequential scans without a self-join or subquery. A basic filter within a sequential scan/loop is the most naive/simplest way of performing queries like these, so postgres falls back to that.
Also, fwiw, BitmapOr is only used with indexes: https://pganalyze.com/docs/explain/other-nodes/bitmap-or.
Re: A SQL Heuristic: ORs Are Expensive
#8This sort of thing is why looking at generated SQL while developing instead of just trusting the ORM to write good queries is so important. I find query planning (and databases in general) to be very difficult to reason about, basically magic. Does anyone have some recommended reading or advice?
It's a big help if you know how to retrieve and interpret execution plans for the database you use.
Re: A SQL Heuristic: ORs Are Expensive
#9Can we agree that this is only applies to queries where all the filter conditions use cols with indexes? If no indexes can be used, a single full table scan with OR surely is faster than multiple full table scans.
Absolutely. Though I don't recall seeing multiple sequential scans without a self-join or subquery. A basic filter within a sequential scan/loop is the most naive/simplest way of performing queries like these, so postgres falls back to that. Also, fwiw, BitmapOr is only used with indexes: https://pganalyze.com/docs/explain/other-nodes/bitmap-or .
That was the extreme case - the multi-scan would be gotten if a casual reader tried your (neat by all means) AND-only query on a non-indexed table (or partially indexed for that matter).
Re: A SQL Heuristic: ORs Are Expensive
#10Yeah, just watch community. Every OR splits the universe, duh.