On May 4, 2007, at 7:08 PM, Tom Lane wrote:
What do you think the output should look like?  The first thought that
comes to mind is to add "method=memory" (or disk or top-N) to the
"actual" annotation:

regression=# explain analyze select * from tenk1 order by fivethous limit 100;
                                                       QUERY PLAN
---------------------------------------------------------------------- -------------------------------------------------- Limit (cost=840.19..840.44 rows=100 width=244) (actual time=140.511..141.604 rows=100 loops=1) -> Sort (cost=840.19..865.19 rows=10000 width=244) (actual time=140.492..140.880 rows=100 loops=1 method=top-N) ^^^^^^^^^^^^
         Sort Key: fivethous
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) (actual time=0.074..51.849 rows=10000 loops=1)
 Total runtime: 143.089 ms
(5 rows)

Another possibility, which could be wedged into explain.c slightly more easily, is to append "Method: top-N" or some such to the Sort Key line,
but I'm not sure that that would look nice.

If the method is disk it would be nice to know how much spilled to disk. That would tell you if it would be worth increasing work_mem, and by how much.

On a related note, it would also be *really* nice if we kept stats on how many sorts or hashes had spilled to disk, perhaps along with how much had spilled. Right now the only way to monitor that in a production system is to setup a cron job to watch pgsql_tmp, which is far from elegant.

I know there's concern about how much we add to the stats file, but I don't think this needs to be on a per-relation basis; per-database should be fine.
--
Jim Nasby                                            [EMAIL PROTECTED]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to