New answers tagged query-performance
0
Perhaps you've noticed the large discrepancy between rows and r_rows for customeritem in the second analysis. According to MariaDB's manual, that table could use an "analyze" itself.
3
Picking up from comments on Erik's answer:
You basically have the ol' optional parameters case. It seems you expect us to tell you which option is best. We can't do that since we aren't in your shoes. What we can do is to outline a few alternatives for you to investigate and then determine which is better your your particular situation.
Branching with IF and ...
13
It's not that you should never use OR in a where clause, it's that the particular pattern you're following in this case is a very bad one. I've written and recorded on the subject:
Optional Parameters and Missing Index Requests
The SQL Server Performance Tasting Menu: Optional Parameters
Additionally, the pattern you chose to replace it with will not work ...
22
Never listen to anyone saying that you should never do X.
Generally, you should not try to outsmart the query optimizer without some really good reasons.
It is true that in certain scenarios too many ORs can lead to a suboptimal1 plan, but you should consider every such scenario individually and only look for workarounds if the original query performance is ...
0
I think this might work... unless you expect multiple duplicates from stuffthatis (in which case, try joining to a subquery of SELECT DISTINCT).
select A.*, B.*
from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
LEFT JOIN stuffthatis c on b.name = c.name
where
(b.ToBe = 1 and a.Being = C.being)
or
(b.ToBe <> 1 and C.name is ...
0
Maybe this query would be better?
We would check stuffThatIs for every row, and then validate if
ToBe=1 and there is a.Being in stuffThatIs OR
ToBe<>1 and there isn't a.Being in stuffThatIs
SELECT *
FROM someStuff a
LEFT JOIN otherStuff b ON (a.x = b.x)
OUTER APPLY (
SELECT 1 [is_inside]
WHERE EXISTS(SELECT *
FROM stuffThatIs x
...
0
You mentioned that it is a queue table, so the data distribution is probably skewed: Most of the rows have status representing completion and ideally just few rows would be candidate for process.
For the query in the image, the ItemStatus values used in the predicate are literals so this is the perfect use case for a filtered index:
CREATE INDEX index_name
...
0
So I changed my query to this and it gives me the result I want :
select t1.lc_num,
t2.brn_name "Advising Bank",
t3.brn_name "Issuing bank"
from TABLE_LC t1
left join BRN t2
on t1.brn_code = t2.brn_code
and t1.brn_role = 1010
left join BRN t3
on t1.brn_code = t3.brn_code
and t1.brn_role = 2020
Actually ...
0
The full object locks which last (usually) for the duration of a transaction and which you can see in pg_locks have info about them stored in shared memory. But access to that shared memory requires the protection of light-weight locks, which should last for only nanoseconds or microseconds while the memory access is actually occuring. What you appear to ...
2
Your query does return the duplicate locations, but does not return the individual points (work orders) with the same location.
This returns those locations again (same as your query, just reformatted in a more compact notation):
select longitudex, latitudey, count(*)
from workorders
group by longitudex, latitudey
having count(*) > 1;
Then this returns ...
1
With Oracle 12.1 or later, add this to the end of the query:
fetch first row only
By the way, count(*) is the standard expression across all SQL variants as far as I'm aware.
For your example, that would be:
select
min(objectid) as min_objectid,
longitudex,
latitudey,
count(*) as count,
min(shape) as min_shape
from
workorders
group ...
1
This is what my final solution looked like, and worked for me.
DO $$
DECLARE
--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';
BEGIN
CREATE TEMP TABLE temp_output ON COMMIT DROP AS
select distinct on (account.id, menu.name, kitchen_item.name)
account.id as "...
0
Yes, DO cannot return results. The docs don't make this particularly obvious, but that's what "returning void" means. DO is really only useful for state-mutating statements (as is demonstrated in the example in the docs).
Is DO required to declare variables to be used in the select? How would I go about returning the results?
SQL has no notion of ...
1
Answer to the question asked
You can simplify the query quite a bit:
SELECT trunc((EXTRACT(epoch FROM max(insert_time) - min(insert_time)) / 3600)::numeric, 2) AS hours -- !
FROM tv_smartdevicemeasurement_mqtt
WHERE (data->>'smart_device_id')::int = 8 -- filter section
AND (data->>'potenza_kw')::float > 1
AND insert_time >= '2020-...
1
It looks like almost all the time is spent just reading the data. I don't think the window function really has anything to do with the poor performance. The timestamp selection would probably be better written as this:
insert_time>='2020-10-01' and insert_time < '2020-11-01'
especially if you had an index it could use, probably on (smart_device_id, ...
1
Better? It depends.
If the primary key of TABLE_LC is (lc_num, brn_role) then you could do something like this:
SELECT
lc_Num
,MAX
(
CASE
WHEN brn_role = 1010 THEN brn_name
ELSE NULL
END
) AS Advising_Bank
,MAX
(
CASE
WHEN brn_role = 2020 THEN brn_name
ELSE NULL
END
) AS ...
0
We noticed a similar behaviour in our system.
And also encountered that writing the query with hardcoded parameters instead of using setParameter() would fixed the issue.
We are using MS SQL Server and after further investigation we noticed the the root cause of our issue is a default configuration of the sql server driver that transmits the query parameters ...
0
You should build an index on (SourceId, ParentKey) in this order.
On the contrary, the index on OperationId may be a small part of the problem, but with the above index you should be fast enough.
0
After messing with this problem for weeks, and even swapping out to an entirely new server, I have somewhat come up with solutions that some might find helpful. This very well may be, and I really hope it is, a symptom of a different problem. Nevertheless, my current answer is that the PostgreSQL 12 with postgis query planner is extremely slow and has ...
3
Let's try this:
WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @myId)
SELECT task.flag as taskFlag, task.note as taskNote, s.id as id, s.seen as seenByReader, MAX(...
2
it seems you need to add date_to column to index on date_from column (create multicolumn index on dates)
your geometry index is not used in both execution plans (probably because the boundaries in filter are outside of the boundaries in index). But it seems with correct multicolumn index you don't need this spatial index at all
Let's try this:
SELECT ...
0
Creating indexes does not ensure that it will be used by the SQL planer, that can estimates that the cost by using the index is over the cost of doing a scan on the table, expecially when you use the "star" (*) in the SELECT clause.
Try to put in the SELECT clause of your SELECT command, only the fewest number of columns that is needed for the ...
2
The way to go is using a GIST index. This sort of index helps checking if a value is contained within a range.
Because you want to filter also by person_id, you will need to install the btree_gist extension. In addition, you should convert the valid_from and valid_until columns to a single tstzrange column, which is a range column that holds timestamp with ...
0
You are seeing the victims. Probably only a few of them are the villains.
Look at "Time". A few 'system' processes may have very large times; look at the next couple -- they may be the ones that started the problem
How big is max_connections? If it is more than a few dozen, it is inviting a log jam.
When a lot of threds are running (check ...
1
It is not inevitable that it will keep using the seq scan. Perhaps at some point it will change (indeed, if the tables are vacuumed and analyzed, I don't get the seq scan in the first place, as the anonymous horse indicates.)
However, if your tables keep growing into the future (rather than into the past) your query will count more and more rows, and this ...
Top 50 recent answers are included
