222
For Postgres 9.1 or later:
CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);
In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:
PostgreSQL index not used for ...
25
The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:
EXPLAIN ANALYZE SELECT f1();
Optimization
PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for ...
21
Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.
Here's a cheap way to get an estimated row count:
SELECT table_rows
FROM information_schema.tables
WHERE table_name='planner_event';
Even if you did select count(id), it might still take a very long time, unless you have a secondary ...
20
Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700? You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either. It could only be used to find the rows that "might" match a WHERE and the server layer (...
18
It is just informing that mongod has chosen to use the _id index.
More specifically: IDHACK means that normal query plan path (evaluation, caching, re-evaluation) was bypassed for the query shape {_id: <value>} which is supported by the required _id index. This is a performance optimization for the common use case of fetching a document by primary ...
18
In your sample code above, the index is explicitly created as NULLS LAST and the query is implicitly running NULLS FIRST (which is the default for ORDER BY .. DESC) so PostgreSQL would need to re-sort the data if it used the index. As a result the index would actually make the query many times slower than even the (already-slow) table scan.
rds-9.6.5 root@...
12
Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria.
Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain ...
11
In addition to the good advice by @Craig and @dezso:
Statistics
The count is 3940689.
Yet, your query plan says:
Seq Scan on posts (cost=0.00..205984.62 rows=**3387494** width=12)
And your count is based on a selection:
Rows Removed by Filter: 404218
4344907 (3940689 + 404218) >> 3387494. Your statistics are not up to date. Something may be ...
11
This has a simple reason.
In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.
There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.
10
Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. It can do this selectively, so some blocks can be converted lossy while others not.
If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information ...
9
In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics.
When you run explain, the rows column will tell you, for each table access, how many rows will be examined by using the intended filter. There are two ways to calculate that: ...
8
Answer is in the article you are referring to:
Here the planner has decided to use a two-step plan: the child plan
node visits an index to find the locations of rows matching the index
condition, and then the upper plan node actually fetches those rows
from the table itself.
Word Output here means physical read of values. Child plan does not read ...
7
PROBLEM #1
Look at the query
select last_name from employees order by last_name;
I don't see a meaningful WHERE clause, and neither does the MySQL Query Optimizer. There is no incentive to use an index.
PROBLEM #2
Look at the query
select last_name from employees force index(idx_last_name) order by last_name;
You gave it an index, but the Query ...
6
The Append has to do some buffer management. It also makes a lot of calls to some clock function (gettimeofday, for example), to satisfy the timing component of EXPLAIN ANALYZE. That overhead might be the dominant time sink.
In your case, the Result node is computing the result of the SUBSTR function. You can see this is in the Output field of the verbose ...
6
Ok, I found out why. Apparently I was querying against the admin database. Always run use <db> guys
6
No, not a feature - it's turning one of these on:
SET SHOWPLAN_TEXT ON;
SET STATISTICS PROFILE ON;
The SHOWPLAN_TEXT option and STATISTICS PROFILE option are well-documented and have been around for over a decade, but I don't know when SSMS started turning it on for the combo of live query plans and actual plans. I would consider it a bug.
Here's the ...
6
If you only want to look at the execution plan, run EXPLAIN without ANALYZE.
ANALYZE will cause the query to be executed, which provides much more useful data, but takes as long as the query runs.
6
Explanation
The all-important difference between the two query plans is the added read=xyz bit in multiple places of the slow version.
Slow:
Buffers: shared hit=116296 read=42298
Fast:
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, ...
5
Erwin's answer is already comprehensive, however:
Range types for timestamps are available in PostgreSQL 9.1 with the Temporal extension from Jeff Davis: https://github.com/jeff-davis/PostgreSQL-Temporal
Note: has limited features (uses Timestamptz, and you can only have the '[)' style overlap afaik). Also, there's lots of other great reasons to upgrade to ...
5
The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index.
A single index over both columns in the WHERE clause:
ALTER TABLE clients ADD KEY(source,added) -- adding this
ALTER TABLE clients ADD KEY(added,source) -- or this
...will usually help you more ...
5
type: index means it's an index scan. That is, it's scanning through an entire index of that table.
An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index.
There are as many ...
5
Sort Method: external merge Disk: 92048kB
Throw more work_mem at the problem. Lots more. Try:
SET LOCAL work_mem = '300MB';
Be aware that if you're running it in lots of concurrent connections you could exhaust system RAM. So SET only in individual sessions.
Your row-count estimate on the aggregate is a bit dodgy (http://explain.depesz.com/s/RXbq) but ...
5
MySql Explain uses the values you provide, literally, to traverse rows of the associated tables. If you provide a constant/key value which is not in the associated table, MySql Explain will stop with this error. Simply query the associated table(s) for values which do exist and provide those in your Explain query and everything will work as expected.
5
In this case, it's doing an Index Scan. As best as I know, the difference between an Index scan and a Bitmap Index/Heap Scan is that the former will read pages in the order defined by the index, while the latter will create a bitmap of pages to read (possibly from multiple indexes), order the results, and read them in [heap] order.
Correct.
There are also ...
5
There are three important parts to reading query plans here,
Did it run. If so,
How many times?
Was it correlated?
Sample Data
You didn't provide any sample data, so let's create some.
CREATE TABLE foo AS
SELECT x FROM generate_series(1,100) AS x;
And, now let's run a basic query with subquery, outside of the possible range of execution.
EXPLAIN ...
answered Jan 7 '17 at 19:17
I Support The Boycott
50.9k32 gold badges181 silver badges382 bronze badges
5
Evan already pointed out that you may have overlooked the (never executed) in the output of EXPLAIN ANALYZE.
A cleaner and more versatile way to write your query in modern Postgres would be with a LATERAL subquery (not necessarily faster):
SELECT id, email
, first_name AS "firstName"
, last_name AS "lastName"
, is_active AS "isActive"
...
5
Some index strategies don't definitely conclude that a tuple meets the criteria. They can definitely and rapidly eliminate most tuples which provably can't meet the criteria (which is where the performance gain comes from) but some of the ones that pass might be false positives, so need to be rechecked.
I guess you are using gin_trgm_ops, which is an ...
4
There's a lot of good material in the comments, but I overlooked something obvious early on that actually makes the answer to the question fairly straightforward.
What I missed was the repeated use of OR in the queries, which I originally mis-read as AND... and this makes a great deal of difference. You're asking for this:
WHERE relDst='24794' OR relSrc='...
4
I suggest to use a trigram index provided by the additional module pg_trgm. Combine that with the length of the string to get a valid pre-selection.
Drop the columns T4,T16 and T64 (faster in a single statement), and run VACUUM FULL or CLUSTER.
Install pg_trgm. Details here:
Full Text Search With PostgreSQL
Create a GiST index on tl and length(tl).
...
4
Unfortunately the answers are as follows:
Is there a unit for cost in an Oracle execution plan?
Not really.
I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage?
Nope.
What does this number stand for?
It's defined like this: (see the glossary)
A numeric internal measure that ...
Only top voted, non community-wiki answers of a minimum length are eligible
