Re: what's the slowest part in the SQL

Thank you Tom very much, that’s the piece of information I miss.

So, should I expect that the nested loop join would be much faster if I cache both tables (use pg_prewarm) into memory as it waives the disk read?

Thanks,
Suya

On 8/10/16, 10:45 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Suya Huang <shuang(at)connexity(dot)com> writes:
> -> Index Scan using idx_order_1_us on order o (cost=0.56..8.58 rows=1 width=30) (actual time=5.814..5.814 rows=0 loops=526)

4 or so ms per row fetched is well within expectation for random access to
spinning-rust media. For example, a 15K RPM drive spins at 4 ms per
revolution, so rotational delay alone would probably explain this number,
never mind needing to do any seeks. So I see nothing even slightly
unexpected here, assuming that the "order" table is large enough that none
of what you need is in RAM already. If you need more performance, look
into SSDs.

(If you have storage kit for which you'd expect better performance than
this, you should start by explaining what it is.)

regards, tom lane