1,489 questions
0
votes
1
answer
206
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
(...
0
votes
0
answers
43
views
Why isn't MySQL using appropriate indexes when available? When forced to use them, the performance difference is dramatic; is this normal?
I've got the following SELECT statement where all columns are properly indexed with the column they're joining on. I.e. all PKs & FKs have a corresponding, single-column index shown later ending ...
- 887
2
votes
1
answer
124
views
Allow query planner to eliminate a left join even when not joining on a unique key
A nice feature of SQL Server's query planner is that if you do not use any columns from a joined table, and the join does not affect the cardinality of the query, it can be eliminated altogether. You ...
- 1,622
1
vote
2
answers
148
views
Query hint for IndexScan shows no effect, table still uses SeqScan
We have two tables in a Postgres DB. Table1 is a small temp table, Table2 is a huge regular table (> 1 million rows):
-- small, temp:
CREATE TEMPORARY TABLE Table1 (
uid VARCHAR(15)
, idx INTEGER
...
- 39
-3
votes
1
answer
72
views
Query Plan for different parameter length [closed]
I have a parametized query that receives a string as input. This string varies in length, but never smaller than 10 digits, never longer than 14.
When I look at sys.dm_exec_query_stats I can see that ...
- 11.5k
0
votes
1
answer
87
views
oracle sql query brings the result for a long time
I have written a query in oracle sql as follows. But the sql query brings the result for a long time. For example, when searching for a customer, the result comes in 4 minutes. Please help me to ...
- 19
0
votes
1
answer
93
views
When the index is the same, the database version is the same, but the same SQL statement has different execution plans on different hosts
I am currently using the version 8 of Dameng database. In SQL query sentences, I am dealing with a very large table. I have created the same unique index on this table in the development environment, ...
1
vote
1
answer
97
views
Fetch the top 5 efficient query plans for a specific object in SQL Server
I am trying to analyze the performance of different query execution plans for a specific object in SQL Server. My goal is to retrieve the top 5 most efficient query plans so that I can compare them ...
- 107
0
votes
0
answers
190
views
Execution Plan keeps showing a missing Index when I have the index
I have already created the missing index on the table and the plan keeps telling me there is a missing index?
<MissingIndexes>
<MissingIndexGroup Impact="10.6268">
<...
- 17
1
vote
1
answer
305
views
Full table scan in nested query in Snowflake
After particular time on solving task and reviewing Snowflake documentation I noticed potential improvement in query for readability and possibly performance improvement. My query is using nested ...
0
votes
0
answers
85
views
Elapsed Time in QEP returned from sys.dm_exec_query_plan_stats is different from the one in Actual Execution Plan returned by SSMS
I am trying to use the LAST_QUERY_PLAN_STATS property to save the QEP Actual of each executed statement in my repository,
using DMV sys.dm_exec_query_plan_stats I am able to intercept it and save it ...
- 1
2
votes
1
answer
579
views
Do I trust "cost" or "actual time" more when using Postgres EXPLAIN ANALYZE?
I'm trying to improve the performance of one of our most expensive queries. I'm running EXPLAIN ANALYZE on the sandbox postgres 15 database, which has a much smaller dataset than the production ...
0
votes
1
answer
220
views
Simple insert times out in application, but fast in SSMS
In my application I have a simple insert, when captured with profiler it looks like this
insert into ford.tblFordCompoundFlowVehicle
(FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, ...
- 12.3k
0
votes
2
answers
192
views
Used statistics from XML plan cache for query with given QueryPlanHash
I'm using SQL Server 2016/2019. I want to extract (for given @QueryPlanHash BINARY(8) = 0x397CEDB37FA0E1D2) from execution plan XML in cache - which statistics was used to generate that plan.
This ...
- 21
0
votes
1
answer
191
views
MySQL(5.7.26) query optimization, why select * is much faster that select id in my leading wildcard query?
I have a leading wildcard query I know it is hard to optimize. I know if I use only the trailing wildcard I can do some optimization. But our client wants the leading wildcard because when searching ...
- 12.8k
0
votes
1
answer
90
views
How to ensure use of an index?
I have the following query:
SELECT cp.ID_Case
FROM dbo.CaseParty cp (NOLOCK)
JOIN dbo.Client cli (NOLOCK) ON CASE
WHEN cli.ClientType = 'atty' AND cp.ID_ClientAttorney = cli.ID_Client THEN 1
...
- 62.1k
3
votes
2
answers
184
views
Estimated execution plan alternatives
As far as I understand, given a query, Oracle tries different approaches based on some heuristics, where I guess the most driving factor is minimizing the number of rows to work with, and then chooses ...
- 12.5k
2
votes
1
answer
734
views
How to get EXPLAIN output with a SELECT statement?
I'm trying to grab a query plan for my query. However, I do not have direct access to the db and can only run SELECT statements on my end.
Is there a way to wrap SELECT around EXPLAIN, maybe even ...
-1
votes
2
answers
122
views
Understanding the Execution Plan Discrepancy in a MySQL Query Involving Left Joins and Filtering
I wrote a SQL query to fetch all movies and, if present, include the name 'Brad Pitt' for movies where he's a cast member. The query is as follows:
select m.title, p_cast.person_name
From movie m ...
0
votes
1
answer
58
views
Why is this Postgresql UPDATE statement so slow even when it doesn't update any rows? [closed]
I see that a full table scan is planned, however it's never executed and the UPDATE takes a long time anyway. Why??
Here's the EXPLAIN output
Update on public.hone_cohortuser (cost=3180.32..8951.51 ...
- 1,484
1
vote
1
answer
239
views
How do I make postgres use an index when the query condition references a subquery?
I have a table 'analytics_event' (with 9.7M rows) which I need to query for records 'created' after a given time. The table has various indexes which are being ignored in favour of a seq scan over the ...
- 13
1
vote
1
answer
274
views
What does Type "ref|filter" mean in mariaDB Explain Output
Trying to troubleshoot a MariaDB query. We did an explain select on the select. A couple of the rows in the output have value ref|filter for the type column.
What does this mean? The MariaDB docs ...
- 6,812
2
votes
1
answer
966
views
PostgreSQL Hash Join vs Nested Loop and hash index
I'm testing how join works with hash index in PostgreSQL 16.2. Here is a test table. Just 2 columns with numbers in text format.
create table join_test (
pk varchar(20),
fk varchar(20));
...
- 370
2
votes
0
answers
318
views
Performance Issues After Migrating from MariaDB 10.3.39 to 10.6.16 Due to Changes in Query Execution Plan
We recently migrated our database from MariaDB version 10.3.39 to 10.6.16 and have encountered significant performance issues with many of our older, mostly unoptimized queries. We've isolated one ...
- 21
1
vote
2
answers
408
views
How to improve sort performance in a huge dataset of 20 million rows?
So I have this query which is extremely slow to run -
SELECT
"conversation".*,
"teamBotLocation"."id" AS "teamBotLocation.id",
"teamBotLocation&...
- 13