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@xxxxxxxxxxxxx> wrote: Suya Huang <shuang@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
