The all-important difference between the two query plans is the added read=xyz bit in multiple places of the slow version.
Buffers: shared hit=116296 read=42298
Buffers: shared hit=158122
This tells you that Postgres encountered data (or index) pages that were not cached, yet. Repeat the slow query (possibly more than one time, ...
There are two things that jump out immediately with the information_schema Details:
When there is a JOIN between MyISAM and InnoDB, the InnoDB tables will suffer from table-level lock behavior instead of row-level locking because of how MyISAM needs to operate.
The query engine in MySQL relies a great deal on ...
You looked at the estimated costs of the query and saw that it was less, but you overlooked two important things. The actual measured time was longer (although not by much), not shorter. And the actual row count was off by a factor of 32 from the estimate. Both of these are pretty important flags.
So while you apparently did the EXPLAIN with ANALYZE, you ...
EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original.
EXPLAIN (estimating the cost) suggests 40,089.36 vs. 189,883.92 unicorn points.
But EXPLAIN ANALYZE (measuring actual execution times) disagrees and shows 2,502.031 ms vs. 1,835.193 ms, so around 1/3 slower. There can be many reasons why the ...
The number is a worst-case estimate. Remember that EXPLAIN doesn't typically read the data, so it has no idea if your data contains only a short string of 1-byte characters on average, or if it contains a full 64 characters each 3-bytes per character.
The performance of the query is much more influenced during execution by the number of pages read, and that ...
The secret is in the Rows Removed by Filter: 10115028:
It takes the sequential scan 17 seconds to find and return the first result row.
The optimizer has no idea how long it takes until the first row passes the filter. Since it doesn't make any difference for the quality of the plan, it just sets the startup cost to 0.
Both plans work the same: each of the ...
Using intersect almost always means that you should add a composite index.
where `product`.`available` > 0
and `product`.`is_bought_copy` is null
and `product`.`deleted_at` is null
INDEX(is_bought_copy, deleted_at, -- in either order, first since "="
available) -- last, since "range"
That index ...
The documentation clearly stats:
Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement, whose execution plan you wish to see.
Other statements are not supported.
From the documentation: https://www.postgresql.org/docs/9.1/sql-explain.html, there is no EXPLAIN for ALTER command.
Important: Keep in mind that the statement is actually executed when
the ANALYZE option is used. Although EXPLAIN will discard any output
that a SELECT would return, other side effects of the statement will
happen as usual. If you wish to use ...
You need to run EXPLAIN (ANALYZE, BUFFERS).
Beware that that actually executes the DELETE, so run it in a transaction and ROLLBACK afterwards.
That will show you information about foreign key constraints unless they are deferred, in which case they are executed at commit time.
I'm still curious if it's possible to get costs of a failed query.
In this case, ...
These are likely to help with performance (but I can't tell by how much):
cc_queue: INDEX(status, rid, called) -- (and toss INDEX(status))
calendar: INDEX(event, rid, time) -- see below
cc_queue: INDEX(rid, sent) -- (and toss INDEX(rid))
INDEX(event(333)) is rarely useful. If you have 5.7 or later, that "index prefix" is not ...
It thinks the generic plan you show (with the $1 in it) will be slightly faster than the custom plan (with the actual value in it), 104.57 vs 105.26. So after running a number of custom plans and thinking there is no benefit, it doesn't think it is worthwhile making a new custom plan each time. Of course the estimate is way off, but the estimate is what it ...
"Using temporary; Using filesort" is usually put on the first Explain line, regardless of which table needs them.
There could be more than one "sort". Use EXPLAIN FORMAT=JSON SELECT ... to get this type of detail.
The "file" in "filesort" does not necessarily mean that the sorting is done on disk. When possible, it ...
The easiest thing would be to just get rid of the GiST index. It can't be misused if it doesn't exist. Assuming you created that index for a good (but un-shown) reason and still need it, then if your goal is not to change the original query, I think the best shot is to create a new index:
USING gist (date_range, product_id);
USING gist (...
It may be that all you need is to avoid using the date-range index. I would have done it like this on Oracle:
inner join product_occupancy_items l0_ on l1_.id = l0_.id
inner join products l2_ on l0_.product_id = l2_.id
l2_.customer_id = 'a19917c2-5ee8-47c2-a757-7799c0e54b0d'
and l0_.date_range + 0 ...
You can create an index that is specifically designed for the query:
CREATE INDEX ON product (product_id, item_created_on)
INCLUDE (item_cash_staked, item_cash_won)
WHERE item_rejection_code_id IS NULL;
That should get you and index-only scan.
With old PostgreSQL versions, you can add the columns to the index instead:
CREATE INDEX ON ...
It's OK for it to fail (and, for my logic, revert the whole transaction), but I don't want this failure to take 2ms instead of 40 seconds.
Running the actual DELETE query (with EXPLAIN ANALYZE wrapper or not) is considerably more expensive than checking with a SELECT whether any FK reference will prevent the operation.
Identify FK constraints pointing to ...
select systemv0_.id as id1_11_0_
, systemv0_.createdTs as createdT2_11_0_
, systemv0_.systemType as syste3_11_0_
, systemv0_.foundProblemsCount as foundPro4_11_0_
, systemv0_.groupid as groupid8_11_0_
"systemv0_" might be a table name (but I suspect that's unlikely).
"systemv0_" might be a Correlation Name (or "alias") for ...
Those are not the table names, but aliases. You can use them to make a query more readable:
SELECT some.col1, another.col2
FROM some_really_long_table_name AS some
JOIN another_terrible_table_name AS another
WHERE some.col4 = 42;
But of course you can also abuse aliases to obfuscate and inflate a query, as some object-relational ...
Since parallel_leader_participation is at its default value off, the leader participates in the sequential scan. The I/O times of the worker processes are listed individually, but the I/O time of the leader can only be found by subtracting the workers' times from the total time.
The parallel sequential scan took 65026.980 milliseconds, almost all of the time....
In operation 21, the optimizer estimates 58 rows to be returned from table TTAMSHIFTDAILY.
Using these 58 rows, in operation 22, the database will access index IX_TUNED_TTADATTENDANCE_1 58 times, estimating 1360 rows to be returned. But that is not the total amount of rows it expects to return, because it is a nested loops join. That estimate is about 1 ...
I've been using RLS in production for a few years and my experience with it is that, despite being very useful, it's a bit of a leaky abstraction.
I believe this question as well as the most voted answer will help you understand why it's hard to make correct assumptions about query plans that involve tables with RLS, especially when JOINs are involved.