Re: Why could different data in a table be processed with different performance?

> is the length of the text equally distributed over the 2 partitions?
Not 100% equally, but to me it does not seem to be a big deal...
Considering the ranges independently:
First range: ~70% < 10 KB, ~25% for 10-20 KB, ~3% for 20-30 KB, everything
else is less than 1% (with 10 KB steps).
Second range: ~80% < 10 KB, ~18% for 10-20 KB, ~2% for 20-30 KB, everything
else is less than 1% (with 10 KB steps).

>From what you posted, the first query retrieves 5005 rows, but the second
2416. It might be helpful if we are able to compare 5000 vs 5000
Yes it was just an example, here are the plans for approximately same
number of rows:

Aggregate (cost=9210.12..9210.13 rows=1 width=16) (actual
time=4265.478..4265.479 rows=1 loops=1)
Buffers: shared hit=27027 read=4311
I/O Timings: read=2738.728
-> Index Scan using articles_pkey on articles (cost=0.57..9143.40
rows=5338 width=107) (actual time=12.254..873.081 rows=5001 loops=1)
Index Cond: ((article_id >= 438030000) AND (article_id <=
438035000))
Buffers: shared hit=4282 read=710
I/O Timings: read=852.547
Planning time: 0.235 ms
Execution time: 4265.554 ms

Aggregate (cost=11794.59..11794.60 rows=1 width=16) (actual
time=62298.559..62298.559 rows=1 loops=1)
Buffers: shared hit=15071 read=14847
I/O Timings: read=60703.859
-> Index Scan using articles_pkey on articles (cost=0.57..11709.13
rows=6837 width=107) (actual time=24.686..24582.221 rows=5417 loops=1)
Index Cond: ((article_id >= '100021040000'::bigint) AND (article_id
<= '100021060000'::bigint))
Buffers: shared hit=195 read=5244
I/O Timings: read=24507.621
Planning time: 0.494 ms
Execution time: 62298.630 ms

If we subtract I/O from total time, we get 1527 ms vs 1596 ms — very close
timings for other than I/O operations (considering slightly higher number
of rows in second case). But I/O time differs dramatically.

> Also is worth noticing that the 'estimated' differs from 'actual' on the
second query. I think that happens because data is differently distributed
over the ranges. Probably the analyzer does not have enough samples to
understand the real distribution.
I think we should not worry about it unless the planner chose poor plan,
should we? Statistics affects on picking a proper plan, but not on
execution of the plan, doesn't it?

> You might try to increase the number of samples (and run analyze)
To be honest, I don't understand it... As I know, in Postgres we have two
options: set column target percentile and set n_distinct. We can't increase
fraction of rows analyzed (like in other DBMSs we can set ANALYZE
percentage explicitly). Moreover, in our case the problem column is PRIMARY
KEY with all distinct values, Could you point me, what exactly should I do?

> or to create partial indexes on the 2 ranges.
Sure, will try it with partial indexes. Should I drop existing PK index, or
ensuring that planner picks range index is enough?

> i would do a sync at the end, after dropping caches.
A bit off-topic, but why? Doing sync may put something to cache again.
https://linux-mm.org/Drop_Caches
https://unix.stackexchange.com/a/82164/309344

> - does the raid controller have a cache?
> - how big is the cache? (when you measure disk speed, that will influence
the result very much, if you do not run the test on big-enough data chunk)
best if is disabled during your tests
I am pretty sure there is some, usually it's several tens of megabytes, but
I ran disk read tests several times with chunks that could not be fit in
the cache and with random offset, so I am pretty sure that something around
500 MB/s is enough reasonably accurate (but it is only for sequential read).

> - is the OS caching disk blocks too? maybe you want to drop everything
from there too.
How can I find it out? And how to drop it? Or you mean hypervisor OS?
Anyway, don't you think that caching specifics could not really explain
these issues?

> I think that you should be pragmatic and try to run the tests on a
physical machine.
I wish I could do it, but hardly it is possible. In some future we may
migrate the DB to physical hosts, but now we need to make it work in
virtual.

> on the VM or on the physical host?
On the VM. The physical host is Windows (no iotop) and I have no access to
it.

Vlad