Yves Dorfsman <yves@zioup.com> writes:
> On 2016-06-10 08:13, Tom Lane wrote:
>> It looks like everything is fine as long as all the data the query needs
>> is already in PG's shared buffers. As soon as it has to go to disk,
>> you're hurting, because disk reads seem to be taking ~10ms on average.
> -> Index Scan using msgs_message_pkey on msgs_message (cost=0.43..8.04 rows=1 width=47) (actual
time=18.550..18.559rows=0 loops=3556)
> Index Cond: (id = msgs_message_labels.message_id)
> Filter: (is_active AND is_handled AND has_labels AND (NOT is_archived) AND (created_on <
'2016-06-1007:11:06.381+00'::timestamp with time zone) AND (org_id = 7))
> Rows Removed by Filter: 1
> Buffers: shared hit=11032 read=3235 dirtied=5
> Do you mean that it reads the index from disk? Or that it looks things up in the index, and fetch data on disk (based
onthat lookup)?
The "reads" here might be either index pages or table pages; we can't tell
from EXPLAIN's statistics. It's probably a good bet that more of them are
table pages than index pages though, just because the index should be a
lot smaller than the table and more fully represented in cache.
As for the numbers, we see that 18.559 * 3556 = 65995 ms were spent in
this indexscan plan node, versus negligible time for the same plan node
when no reads happened. So we can blame pretty much all that time on
the 3235 disk reads, giving an average per read of just over 20ms. Some
of the other plan nodes show lower averages, though, so I was conservative
and said "~10 ms".
regards, tom lane
