I'm trying to use a compound index on a table to assist in creating daily report counts. My table looks like this: CREATE TABLE inquiries ( id bigint NOT NULL, identity_id bigint NOT NULL, received_at timestamp(0) without time zone NOT…
Using Postgres and Postgis I was experiencing a very slow spatial join using st_equals. Out of curiosity, I tried using = instead, and the join was much faster. Using EXPLAIN, I noticed that the st_equals join uses a nested loop,…
Problem Statement: We will be getting a request of a number (11 digits) and have to efficiently lookup in the database and return a single row (last updated) based on range in which it fits. Also, I have already used…
I have customers and visits table, and I would like to know which of these two queries has the better performance: (I have indexes defined for those columns) Query 1 SELECT customers.id as id, COALESCE(v.count, 0) as visits FROM customers…
My SQL knowledge is really basic, and need some help in translating this rather long query (running in a python script querying some AWS athena database, with some f-strings embedded) to plain English or flow chart. I understand concepts of…
I have the following query: SELECT match_main.checked, match_main.match_main_id, match_main.updated, match_main.created FROM match_main LEFT JOIN match_team AS mt1 ON mt1.match_main_id = match_main.match_main_id AND mt1.team_number = 1 AND mt1.version_number = 0 LEFT JOIN match_team AS mt2 ON mt2.match_main_id = match_main.match_main_id AND mt2.team_number…
In Azure SQL database, the same query is completed in 4 seconds when Row Level Security is active, and in 1 second when Row Level Security is disabled. When I compared the execution plans of the queries, I found that…
I have an postgres table in the below structure I want to write a query which will Delete all the records from the table which are older than 90 days and is in COMPLETE status. if any row has a…
I have 2 dedicated servers, S1, 24 cores and 128gb RAM, and S2, 8 cores and 64GB RAM. Both server CPUs have multithreading. On S1 I run cPanel and S2 serves only as a DB server( remote ). Both servers…
Our IP address ranges table has ~2.8 million records, of the following structure: { start_ip: integer, // IPv4 converted to int32 end_ip: integer, // IPv4 converted to int32 country_code: string, country: string, region: string, city: string } There is a…