4

Here's an example plan on explain.depesz.com:

Limit  (cost=65301.950..65301.950 rows=1 width=219) (actual time=886.074..886.074 rows=0 loops=1)
  ->  Sort  (cost=65258.840..65301.950 rows=17243 width=219) (actual time=879.683..885.211 rows=17589 loops=1)
          Sort Key: juliet.romeo
          Sort Method: external merge  Disk: 4664kB
        ->  Hash Join  (cost=30177.210..62214.980 rows=17243 width=219) (actual time=278.986..852.834 rows=17589 loops=1)
                Hash Cond: (whiskey_quebec.whiskey_five = juliet.quebec)
              ->  Bitmap Heap Scan on whiskey_quebec  (cost=326.060..21967.630 rows=17243 width=4) (actual time=7.494..65.956 rows=17589 loops=1)
                      Recheck Cond: (golf = 297)
                    ->  Bitmap Index Scan on kilo  (cost=0.000..321.750 rows=17243 width=0) (actual time=4.638..4.638 rows=17589 loops=1)
                            Index Cond: (golf = 297)
              ->  Hash  (cost=15750.510..15750.510 rows=329651 width=219) (actual time=267.388..267.388 rows=329651 loops=1)
                      Buckets: 1024  Batches: 128  Memory Usage: 679kB
                    ->  Seq Scan on juliet  (cost=0.000..15750.510 rows=329651 width=219) (actual time=0.003..65.704 rows=329651 loops=1)

In what order are these operations executed? Bottom up? Top down?

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85
TheFooProgrammer
  • 2,439
  • 5
  • 28
  • 43
  • 1
    BTW `Sort Method: external merge Disk: 4664kB` means that postgres has wery low `work_mem` configuration. You can get results faster just by seting `work_mem` to something more apropriate. – Ihor Romanchenko Feb 15 '14 at 08:39
  • 1
    A great tool for exploring your explain plans is explain.depesz.com: http://explain.depesz.com/s/nGQo – Scott Marlowe Feb 16 '14 at 03:09

2 Answers2

10

Both direction has sense. From up to down goes a request for data. From down to up goes data. You see a tree - every node has minimal one child. On execution time, node call his children - "send me data" and this call is recursively repeated to leafs - seq scan, index scan, set returned function call, ... that produces data and row by row - are data sended to parents.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
3

I usually start reading the tree from bottom last line with ->, to top.

In your case it obviously needs the data before it can sort it, so sorting is the last thing it does ...

Sequential scan and index lookup: The important thing to look for is usually if it uses an index or not. You can disable sequential scan using SET enable_seqscan = OFF; to force the use of indexes in case you have insufficient data for the planner to use indexes. Remember to put it back !

IMPORTANT: If you use EXPLAIN ANALYSE (or ANALYZE) it will have side-effects !!!

I really recommend you read the official docs, you can also find nice stuff like getting the output as yaml or json:

EXPLAIN (format yaml) your_query_here

Also see: https://explain.depesz.com/ (for a pretty representation)

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85