New answers tagged query-performance
2
SELECT et.id AS event_type_id, count(e."eventTypeId") AS event_count
FROM event_types et
LEFT JOIN events e ON e."eventTypeId" = et.id
WHERE et."projectId" = 142
GROUP BY et.id;
Major points
0. Style
Avoid CaMeL-case names if you can, to make your life and ours easier.
Are PostgreSQL column names case-sensitive?
At the very least use legal table ...
0
You are fundamentally doing a lot of work, and doing a lot work takes a lot of time. It is not clear you can do much better here.
One possibility is that you pre-compute the counts for the whole table:
select bar_id, count(bar_id) from api.foo group by bar_id
And join to that rather than running the subselect inside the "sqrt".
I see no reason to think ...
1
As you mentioned you are trying to get 80% of the rows of your table and it is normal that postgreSQL optimizer decide to scan the table rather than get data from the index. Unfortunately postgres does not support queries with index hints like MS SQL server, so it is not possible to compare the results of index scan vs sequential scan but I am absolutely ...
0
I think that if you dont care about FOO'and can freely drop it, just drop it and make a CTAS using parallel, it will be the fastest. Use the alter sessions sugested by Balazs Papp, maybe add a third one (force parallel DDL).
1
count(i.BrandId) checks BrandId for being not null before counting. If you don't need that check, change to COUNT(*) to count the number of rows in the JOIN. Or perhaps you wanted COUNT(DISTINCT i.BrandId) to count the number of different brands?
Then these indexes will help:
project: INDEX(Date)
projectitem: INDEX(projectid) -- if using ...
2
Uncommitted Transactions
As Dan Guzman mentioned, check for uncommitted transactions (e.g. using DBCC OPENTRAN) and look at the locks held by the blocking session. It may be a query timed out, wasn't rolled back properly, and the pooled connection with the open transaction reused. You need to remember locks are held by the session of that 'unrelated' query ...
1
The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it
That is true; the question is, how many table scans do you think are there?
SQLite documentation will tell you that its optimizer is capable of only nested-loop joins, so regardless of whether it is smart enough to rewrite the EXISTS subquery into a join1 ...
1
Are you running sp_executesql as a parameterised call or are there values being passed in each time that slightly change the text?
You might be seeing the effect of parameter sniffing being used that might generate different plans against each call.
See https://www.scarydba.com/2013/09/05/sp_executesql-parameters-and-parameter-sniffing/
As a result it may be ...
3
Improving INSERT Performance with Direct-Path INSERT
Example:
alter table foo nologging;
alter session force parallel query parallel 8;
alter session force parallel dml parallel 8;
insert /*+ append */ into foo select * from bar where some_unimportant=condition;
1
If I understand what you're doing there (which I may not be 100% .. ) .. it appears you're trying to update REQUEST table, using the other VMI_DIMCUSTOMER table as "input" .. but only if records exist.
If there are not records in VMI_DIMCUSTOMER table, for a given record in REQUEST, you want to flag the record in REQUEST with your "customer not found" ...
1
Assuming your second update needs to only work on the records that were just updated in the merge statement, the following should do the trick:
MERGE INTO request tgt
USING vmi_dimcustomer src
ON (tgt.cust_no = src.customer_num AND tgt.is_checked IS NULL)
WHEN MATCHED THEN
UPDATE SET tgt.first_name = CASE WHEN src.cust_first_name IS NULL THEN '--' ...
1
Here are a few reasons why query is running slow sometimes even though page reads/writes are the same.
The table may be locked which are accessed in the query. You may
check that with sp_who2 or sp_whoisactive.
Autogrowth for your database may have kicked in during that time. Change autogrowth to a higher value.
Index maintenance or statistics update ...
1
MATCH is not the primary villain in the sluggishness; these are:
OR
Using LIKE / RLIKE
pagination via OFFSET
Most of the above prevent the use of any INDEX, hence the query must scan the entire table, thereby being slow for lots of reasons.
Some can be solved in SQL; most require changes to the specifications for the query.
Since the first thing the ...
2
As you add more OR conditions, it thinks it will return more rows. Since there is an ORDER BY and a LIMIT, at some point it thinks that walking a different index, which provides the same ordering as the ORDER BY and stopping early once it hits the LIMIT, will be faster than getting all rows which meet the WHERE clause, and then sorting them into order and ...
4
The problem is that PostgreSQL assumes that more rows will be returned when you specify more OR conditions, so at some point it will think that an index scan won't be faster any more.
You could create a function that extracts the interesting parts of the JSON:
CREATE FUNCTION get_array(jsonb) RETURNS text[]
LANGUAGE sql IMMUTABLE AS
$$SELECT array_agg(x-...
6
You should move the column DateCreated to the key columns in the index LocationId_FirstTrigger.
Then you will get a seek on LocationId and FirstTrigger and a backward scan of the index to locate the row with highest value of DateCreated according to select top(1) ... order by DateCreated desc.
CREATE NONCLUSTERED INDEX [LocationId_FirstTrigger] ON [dbo].[...
7
My question is, why it's still doing key lookup instead of index scan/seek ?
The query specifies that results should be ordered by DateCreated. Since you already had a nonclustered index on DateCreated, the optimizer decided that the cost of doing key lookups was lower than sorting all of the data by DateCreated.
Does this mean key lookup was done due ...
-2
SELECT /* THE TEXT TO BE SEARCHED */ AS searchText,
SUM((LEN(fieldNameOfDB)
- LEN(REPLACE(fieldNameOfDB, 'THE TEXT TO BE SEARCHED', '')))
/LEN('THE TEXT TO BE SEARCHED')) AS noOfOccurances
FROM TableName
0
KEY `parent_id_index` (`parent_id`),
-->
KEY (parent_id, REV, id)
The order is critical. First it filters on parent_id =, then no a range of REV, and finally has id to make the index "covering".
0
Since MAX will never increase due to the JOIN, you might try to filter using EXISTS instead:
SELECT c.id AS cId, MAX(c.REV) AS cRev
FROM Child_AUD c
WHERE EXISTS (
SELECT 1
FROM Parent_AUD p
WHERE p.id = c.parent_id
AND p.REV >= c.REV
)
GROUP BY c.id;
An index like:
CREATE INDEX ON Child_AUD (ID, PARENT_ID, REV);
may help.
1
It's a slightly belated answer, but it might be useful for future readers
Now days (in 10,11,12 versions) we don't need to store same data twice (in WAL by PG and manually). We can use Postgre Logical Decoding mechanics (same as logical replication) to track all or some changes to our data (or send those events to some queue like kafka to analyze later)
1
You've solved your problem, that's great. Recently, I got turned on to the benefits of getting estimated counts rather than "perfect" counts, in cases where it's slow, despite any cleanup efforts you might take. Here's a function adapted ("stolen") from a fine blog post by Laurenz Albe:
DROP FUNCTION IF EXISTS api.row_count_estimate (text);
CREATE OR ...
1
TL;DR I have solved by running this command:
vacuum analyze subscriptions;
After that command the queries take only ~1s instead of ~17s. For a detailed explanation see @Laurenz answer.
Now I run the autovacuum more frequently using these settings in postgresql.conf:
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
Update: the ...
1
The reason vacuuming your table worked is that it enabled an index-only scan to be used efficiently. Unfortunately, autovacuum's scheduler was only designed to handle space-reuse and wraparound prevention, it never got updated to handle the needs of index-only scans when that feature was added. It isn't really clear how to update it in a generic way to ...
3
Fetching and counting 5 million rows is slow business.
There are two problems:
The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with one bit per table row.
It then degrades to storing a bit per 8KB block, which leads to more heap fetches in the bitmap heap scan phase to sort out the false ...
0
By default in MongoDB 3.6.4, WiredTiger will try to keep dirty percentage (size of modified data in the cache relative to configured cache size) to below 5% of configured cache size, and the overall cache usage below 80% of configured cache size. The default cache size is described in WiredTiger memory in the documentation
Once the percentages goes beyond ...
1
Since you're reading every record in the table, the database will simply pull everything you want, wrap it [all] up and send it back across the network to your client machine. The more data you pull, the larger that payload is going to be and the slower it's going to travel.
I frequently run this very simple query
The database is static and the ...
4
Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan.
You're alerted of a slow query that was previously fast
Bad times, I empathize!
You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help
Usually, clearing the cache fixes problems like this if the problem is parameter ...
0
My XML uses Schema.
DATALENGTH will definitely not work.
Even with no Data, you will still have Schema taking up random amounts of space.
This will test for Missing Elements/Nodes (works with or without Schema):
SELECT ISNULL(@ListNoteTypeCode.exist('*:BulletPoint/*:NoteTypeCode'), 0)[HasRows]
With that out of the way, the purpose of your question was to ...
Top 50 recent answers are included
