All Questions
Tagged with sqlperformance or query-optimization
11,572 questions
0
votes
1
answer
59
views
Should SQL filters be applied before or after vector similarity search in PostgreSQL
PostgreSQL tables sometimes store embeddings alongside relational columns and run vector similarity search with additional filters.
Example schema
documents
id bigserial primary key
tenant_id int
...
Best practices
0
votes
3
replies
93
views
Do SQL databases apply WHERE filters before joins?
from a in xx
join b in yy on a.someprop equals b.someprop
join c in zz on a.someprop1 equals c.someprop
where a.someprop2.Contains(strval)
I’m trying to understand how SQL query execution works ...
- 175
3
votes
2
answers
152
views
Can PostgreSQL optimizer carry `ORDER BY` out of `JOIN` subquery?
There is a "theoretical" query:
SELECT * FROM a
JOIN (
SELECT b.pk, b.not_pk
FROM b
ORDER BY b.not_pk
) AS b2
USING (pk)
and EXPLAIN shows Sort on the whole b.
Can it be carried ...
- 435
0
votes
0
answers
70
views
How to achieve dynamic partition pruning in BigQuery TVF using a subquery lookup?
I have a Table Valued Function (TVF) that takes an array of IDs as input.
source_table: A very large table (TBs) partitioned by day on capturedTimestamp.
selection_table: A small lookup table that ...
Tooling
0
votes
3
replies
57
views
How to enforce dynamic partition pruning in BigQuery TVF using a subquery lookup?
I have a table-valued function (TVF) that takes an array of IDs as input.
source_table: a very large table (TBs) partitioned by day on capturedTimestamp
selection_table: a small lookup table that ...
0
votes
0
answers
89
views
How to update a large number of mongodb documents in a tree structure?
I'm using mongo db with payloadcms and I have tree like structure.
We have a collection called nodes. A node have children as array and a parent id.
Now I want to change the node status like marking ...
1
vote
1
answer
153
views
Make MySQL evaluate subquery first
SELECT tt.trans_type_name AS transaction_type,
trans.transaction_time,
a.trans_action_name AS transaction_action_name,
trans.transaction_notes
FROM(SELECT Cast(...
- 175
3
votes
1
answer
112
views
Cause of and means to avoid "flat BNL join" in mariadb 10.11
I'm trying to help a user in another stackoverflow question and have bumped by head into a strange behaviour.
As I rarely use MariaDB, I'm opening another question to investigate the behaviour,
...
- 87.6k
1
vote
1
answer
67
views
AWS Glue Script Scanning Entire Table Despite Date Filter
I have written a small Glue script that fetches some data between two dates, but I found that it scans the entire table instead of just the data within the specified time range. I also tried creating ...
- 11
Advice
0
votes
6
replies
100
views
Why MariaDB doesn't use newly added index?
MariaDB version is 10.4.34.
The query looks like:
SELECT bet.*
FROM Bet bet
WHERE bet.placed >= '2025-10-29T00:00:00'
AND bet.placed <= '2025-10-29T23:59:59'
AND EXISTS (
SELECT 1 FROM ...
- 18.8k
0
votes
1
answer
216
views
Make Postgres query fast without UNION
The ORDMBS is PostgreSQL 17.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit.
I have big table (about 150 GB), partitioned.
Full table description:
CREATE TABLE table_partition
(...
2
votes
2
answers
132
views
Pros and cons of `INSERT INTO` versus `UNION ALL`
I am working with an Oracle database in which each year's data is stored in different tables. I am not the DBA so I cannot change that. Also, I do not have the permission to consult execution plans or ...
- 553
3
votes
0
answers
107
views
Azure Cosmos: MongoDB sharded cluster: $in query performance degrades with fewer values
I'm facing a counter-intuitive performance issue with my MongoDB sharded cluster where queries with fewer values in an $in clause are significantly slower than queries with more values.
The Issue:
...
- 31
0
votes
3
answers
135
views
How to update by the other GROUPed table values effectively?
I have the tables Invoices (with No_ key) and the related InvoiceLines (with [Document No_] key that bounds to their invoices). The subset of invoice numbers is inserted into the table variable (no ...
- 21.1k
-4
votes
1
answer
64
views
How to precompute nested date ranges efficiently to optimize range filtering and pagination? [closed]
📝 Body
I have a Mongo collection CollectionA where each top-level object contains a nested array of meetings now each meetings have start and end times, for example:
CollectionA = [
{
&...
- 51