Optimizing queries. The basics of EXPLAIN in PostgreSQL (part 3)


Podolzhayut to publish the author's processing Understanding EXPLAIN from Guillaume Lelarge.
Again note that some information has been omitted for brevity, so I strongly recommend to read the original.
Previous part:

Part 1
Part 2

ORDER BY


the 
DROP INDEX foo_c1_idx;
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01 120493.04..rows=1000010 width=37) (actual time=..571.591 651.524 rows=1000010 loops=1)
Sort Key: c1
Sort Method: external merge Disk: 45952kB
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.007..62.041 rows=1000010 loops=1)
Total runtime: 690.984 ms
(5 rows)

First is Seq Scan of table foo. Then sorting Sort. The EXPLAIN command sign -> indicates a hierarchy of action (node). The sooner action is executed, the bomore indented it is displayed.
the Sort Key — the sort term.
the Sort Method: external merge Disk — sorting uses a temporary file on the disk 45952kB.

Please understand the topic to clarify the distinction between the external merge and external sort.

Check with the BUFFERS option:
the 
EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01 120493.04..rows=1000010 width=37) (actual time=..568.412 652.308 rows=1000010 loops=1)
Sort Key: c1
Sort Method: external merge Disk: 45952kB
Buffers: shared hit=8334, temp read=5745 written=5745
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.010..68.203 rows=1000010 loops=1)
Buffers: shared hit=8334
Total runtime: 698.032 ms
(7 rows)

Indeed, temp read=5745 written=5745 — the temporary file was written and read 5745 blocks 8Kb = 45960Kb. Operations 8334 units were produced in the cache.

Operations with the file system slower than operations in RAM.
Try to increase the amount of memory used work_mem:
the 
SET work_mem TO '200MB';
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Sort (cost=117993.01 120493.04..rows=1000010 width=37) (actual time=..265.301 296.777 rows=1000010 loops=1)
Sort Key: c1
Sort Method: quicksort Memory: 102702kB
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.006..57.836 rows=1000010 loops=1)
Total runtime: 328.746 ms
(5 rows)

the Sort Method: quicksort Memory: 102702kB — sorting is entirely held in memory.

Index:
the 
CREATE INDEX ON foo(c1);
EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
— Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.023..126.076 rows=1000010 loops=1)
Total runtime: 153.452 ms
(2 rows)

Of action remains only Index Scan, which significantly affected the speed of the query.

LIMIT


Remove previously created index.
the 
DROP INDEX foo_c2_idx1;
EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM foo WHERE c2 LIKE 'ab%';

QUERY PLAN
— Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.033..94.757 rows=3824 loops=1)
Filter: (c2 ~~ 'ab%'::text)
Rows Removed by Filter: 996186
Buffers: shared hit=8334
Total runtime: 94.924 ms
(5 rows)

Expected, use Seq Scan and Filter.

the 
EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM foo WHERE c2 LIKE 'ab%' LIMIT 10;

QUERY PLAN
Limit (cost=0.00..2083.41 rows=10 width=37) (actual time=0.037..0.607 rows=10 loops=1)
Buffers: shared hit=26
-> Seq Scan on foo (cost=0.00..20834.12 rows=100 width=37) (actual time=0.031..0.599 rows=10 loops=1)
Filter: (c2 ~~ 'ab%'::text)
Rows Removed by Filter: 3053
Buffers: shared hit=26
Total runtime: 0.628 ms
(7 rows)

Scan Seq Scan table rows and the comparison Filter with the condition. As soon as 10 records that meet the condition, the scan will end. In our case, in order to obtain 10 rows of the result had to read not all table, but only records 3063, 3053 of them were rejected (Rows Removed by Filter).
The same happens when Index Scan.

JOIN


Create a new table, raise her stats.
the 
CREATE TABLE bar (c1 integer, c2 boolean);
INSERT INTO bar
SELECT i, i%2=1
FROM generate_series(1, 500000) AS i;
ANALYZE bar;


Query on the two tables
the 
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..67.951 rows=1000010 loops=1)
-> Hash (cost=7213.00 7213.00..rows=500000 width=5) (actual time=..87.352 87.352 rows=500000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 18067kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.233 rows=500000 loops=1)
Total runtime: 920.967 ms
(7 rows)

First visible (Seq Scan) table bar. For each row calculates the hash (Hash).
Then scanned Seq Scan table foo, and for each row of this table calculates the hash and compares it (Hash Join) hash table bar condition of Hash Cond. If a match is found, the system displays the resulting string, otherwise the string will be ignored.
Used 18067kB in memory for the host hash table bar.

Add index
the 
CREATE INDEX ON bar(c1);
EXPLAIN (ANALYZE)
SELECT * FROM foo JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Join (cost=1.69..39879.71 rows=500000 width=42) (actual time=0.037..263.357 rows=500010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.019..58.920 rows=500011 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..71.719 rows=500010 loops=1)
Total runtime: 283.549 ms
(5 rows)

Hash is not used. the Merge Join and Index Scan for indexes on both tables give impressive performance gains.

LEFT JOIN:
the 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Hash Left Join (cost=13463.00 49297.22..rows=1000010 width=42) (actual time=..82.682 926.331 rows=1000010 loops=1)
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.004..68.763 rows=1000010 loops=1)
-> Hash (cost=7213.00 7213.00..rows=500000 width=5) (actual time=82.625 82.625..rows=500000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 18067kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.003..31.890 rows=500000 loops=1)
Total runtime: 950.625 ms
(7 rows)

Seq Scan?
Let's see what the results are, if you deny Seq Scan.
the 
SET enable_seqscan TO off; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.024..353.819 rows=1000010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.011..112.095 rows=1000010 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.008..63.125 rows=500010 loops=1)
Total runtime: 378.603 ms
(5 rows)

According to the scheduler, using indexes expensive than the use of hashes. This is possible with a large enough amount of allocated memory. Remember, we were increasing work_mem?
But if memory is in short supply, the scheduler will behave differently:
the 
SET work_mem TO '15MB';
SET enable_seqscan TO ON; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Merge Left Join (cost=0.85..58290.02 rows=1000010 width=42) (actual time=0.014..376.395 rows=1000010 loops=1)
Merge Cond: (foo.c1 = bar.c1)
-> Index Scan using foo_c1_idx1 on foo (cost=0.42..34327.57 rows=1000010 width=37) (actual time=0.005..124.698 rows=1000010 loops=1)
-> Index Scan using bar_c1_idx on bar (cost=0.42..15212.42 rows=500000 width=5) (actual time=0.006..66.813 rows=500010 loops=1)
Total runtime: 401.990 ms
(5 rows)

And what will be the output of EXPLAIN when prohibited Index Scan?
the 
SET work_mem TO '15MB';
SET enable_indexscan TO off; 
EXPLAIN (ANALYZE)
SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1;

QUERY PLAN
— Hash Left Join (cost=15417.00 63831.18..rows=1000010 width=42) (actual time=..93.440 712.056 rows=1000010 loops=1)
Hash Cond: (foo.c1 = bar.c1)
-> Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.008..65.901 rows=1000010 loops=1)
-> Hash (cost=7213.00 7213.00..rows=500000 width=5) (actual time=93.308 93.308..rows=500000 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 9045kB
-> Seq Scan on bar (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.007..33.718 rows=500000 loops=1)
Total runtime: 736.726 ms
(7 rows)

the cost has clearly increased. The reason is Batches: 2. All the hash does not fit in memory, it had to be split into 2 packages, 9045kB.

Here again asking for help guru. Explain why LEFT JOIN and sufficient work_mem, use Merge Left Join more expensive than Hash Left Join?

To stop.

UPD.
A lot of useful info about the indexes on PostgreSQL told Oleg Bartunov and Alexander Korotkov.

Will make a link here for recent articles from the PostgreSQL PostgreSQL Indexes, part 2, part 3. There are a lot of things explained.
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Why I left Google Zurich

the Backstory The question I often hear from different people and now I will try to answer it. Probably for anybody not a secret, but still: how does Google? They recruit the best engineers pay them large enough (even for Switzerland) salary and create an amazing environment for work, free food, massage at the office, the phantasmagoric recreation area , childrens slide down directly into the dining room, etc. This is very cool, like to write journalists, but it doesn't matter. Well, how many times will you walk down the hall on the hill and is it important? Free food has led to the fact that the majority of Googlers fat pounds five after a job, thus denying yourself the pleasure to go to lunch every day in a new restaurant. What's more determines how good you feel at work, free food, massage, design zones, the rest or the work that you do? I'm sure that's really important what you do every day and how this activity brings joy. Google hires the b
Далее...

2000 3000 icons ready — become a sponsor! (the table of orders)

Изображение
Not so long ago on habré was top that led you to our website 17 thousand visitors for two days (11,828+5,100 on October 21, 2010). With new year 2009 this is not the first wave, and the last icons will be drawn in the first quarter of 2012. Best reseller hosting providers . Not enough more 1000 icons for best email hosting . Are you willing to offer metaphors, of which we still have not enough? Then follow me... Gebrettert from 17K inferior reddit 49k (but for four days), but still is a decent part of the order of 200k visitors who have downloaded a set. Initially I just wanted to beat set Yusuke , then not counted, and 2000 icons and there was only 16m in size. Now I just want to make the largest (and having the maximum number of unique metaphors) set of icons in the world . Well, a few minnamoro like these 2 for wordpress admin . Today drawn 2040 icon, cut 2000 more smoothly. I want to draw flags and more file type icons, but still pieces of 40
Далее...

New web-interface for statistics and listen to the calls for IP PBX Asterisk

Изображение
the Idea of writing web-interface for statistics and listen to the calls for IP PBX Asterisk is not left me for several years now. The solutions found in Internet, are not satisfied on certain criteria — somewhere lacked functionality, some of them are not pleasing to the eye. And so, armed with technology stack and riding warhorse provided by ServerClub I went. The result of my journey has become a new interface , with charts, graphs and the ability to download and listen to the calls. I will not further bore you with words, here are a couple of screenshots: And under the cut are waiting for you a video guide on the interface, necessary settings and detailed description of the available functionality. the Interface. That post didn't look like a bed sheet from the screenshots, I made a little slideshow where you can see the interface. Description. The already existing plans. Currently implemented the following functionality: Incoming calls:
Далее...