pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedreducing random_page_cost from 4 to 2 to force index scan
43+ messages / 17 participants
[nested] [flat]
* reducing random_page_cost from 4 to 2 to force index scan
@ 2011-04-26 09:49 Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 43+ messages in thread
From: Sok Ann Yap @ 2011-04-26 09:49 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am using PostgreSQL 9.0. There is a salutations table with 44 rows,
and a contacts table with more than a million rows. The contacts table
has a nullable (only 0.002% null) salutation_id column, referencing
salutations.id.
With this query:
SELECT
salutations.id,
salutations.name,
salutations.description,
EXISTS (
SELECT 1
FROM contacts
WHERE salutations.id = contacts.salutation_id
) AS in_use
FROM salutations
I have to reduce random_page_cost from 4 to 2 to force index scan.
EXPLAIN ANALYSE output with random_page_cost = 4:
Seq Scan on salutations (cost=0.00..50.51 rows=44 width=229) (actual
time=0.188..3844.037 rows=44 loops=1)
SubPlan 1
-> Seq Scan on contacts (cost=0.00..64578.41 rows=57906
width=0) (actual time=87.358..87.358 rows=1 loops=44)
Filter: ($0 = salutation_id)
Total runtime: 3844.113 ms
EXPLAIN ANALYSE output with random_page_cost = 4, enable_seqscan = 0:
Seq Scan on salutations (cost=10000000000.00..10000000095.42 rows=44
width=229) (actual time=0.053..0.542 rows=44 loops=1)
SubPlan 1
-> Index Scan using ix_contacts_salutation_id on contacts
(cost=0.00..123682.07 rows=57906 width=0) (actual time=0.011..0.011
rows=1 loops=44)
Index Cond: ($0 = salutation_id)
Total runtime: 0.592 ms
EXPLAIN ANALYSE output with random_page_cost = 2:
Seq Scan on salutations (cost=0.00..48.87 rows=44 width=229) (actual
time=0.053..0.541 rows=44 loops=1)
SubPlan 1
-> Index Scan using ix_contacts_salutation_id on contacts
(cost=0.00..62423.45 rows=57906 width=0) (actual time=0.011..0.011
rows=1 loops=44)
Index Cond: ($0 = salutation_id)
Total runtime: 0.594 ms
So, index scan wins by a very small margin over sequential scan after
the tuning. I am a bit puzzled because index scan is more than 3000
times faster in this case, but the estimated costs are about the same.
Did I do something wrong?
Regards,
Yap
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
@ 2011-04-26 21:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-26 23:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 43+ messages in thread
From: Kevin Grittner @ 2011-04-26 21:37 UTC (permalink / raw)
To: Sok Ann Yap <sokann@gmail.com>; pgsql-performance
Sok Ann Yap <sokann@gmail.com> wrote:
> So, index scan wins by a very small margin over sequential scan
> after the tuning. I am a bit puzzled because index scan is more
> than 3000 times faster in this case, but the estimated costs are
> about the same. Did I do something wrong?
Tuning is generally needed to get best performance from PostgreSQL.
Needing to reduce random_page_cost is not unusual in situations
where a good portion of the active data is in cache (between
shared_buffers and the OS cache). Please show us your overall
configuration and give a description of the hardware (how many of
what kind of cores, how much RAM, what sort of storage system). The
configuration part can be obtained by running the query on this page
and pasting the result into your next post:
http://wiki.postgresql.org/wiki/Server_Configuration
There are probably some other configuration adjustments you could do
to ensure that good plans are chosen.
-Kevin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-26 23:23 ` Sok Ann Yap <sokann@gmail.com>
1 sibling, 0 replies; 43+ messages in thread
From: Sok Ann Yap @ 2011-04-26 23:23 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On Wed, Apr 27, 2011 at 5:37 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap <sokann@gmail.com> wrote:
>
>> So, index scan wins by a very small margin over sequential scan
>> after the tuning. I am a bit puzzled because index scan is more
>> than 3000 times faster in this case, but the estimated costs are
>> about the same. Did I do something wrong?
>
> Tuning is generally needed to get best performance from PostgreSQL.
> Needing to reduce random_page_cost is not unusual in situations
> where a good portion of the active data is in cache (between
> shared_buffers and the OS cache). Please show us your overall
> configuration and give a description of the hardware (how many of
> what kind of cores, how much RAM, what sort of storage system). The
> configuration part can be obtained by running the query on this page
> and pasting the result into your next post:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> There are probably some other configuration adjustments you could do
> to ensure that good plans are chosen.
>
> -Kevin
>
Here's the configuration (this is just a low end laptop):
name |
current_setting
----------------------------+-------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0, pie-0.4.5)
4.5.2, 64-bit
checkpoint_segments | 16
default_statistics_target | 10000
effective_cache_size | 512MB
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_destination | syslog
log_min_duration_statement | 0
maintenance_work_mem | 256MB
max_connections | 100
max_stack_depth | 2MB
port | 5432
random_page_cost | 4
server_encoding | UTF8
shared_buffers | 256MB
silent_mode | on
TimeZone | Asia/Kuala_Lumpur
wal_buffers | 1MB
work_mem | 32MB
(20 rows)
The thing is, the query I posted was fairly simple (I think), and
PostgreSQL should be able to choose the 3000+ times faster index scan
with the default random_page_cost of 4. If I need to reduce it to 2
when using a 5.4k rpm slow disk, what is random_page_cost = 4 good
for?
(Sorry for the double message, I forgot to CC the list in the first one)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-27 01:04 ` Merlin Moncure <mmoncure@gmail.com>
2011-04-27 07:22 ` Re: reducing random_page_cost from 4 to 2 to force index scan Claudio Freire <klaussfreire@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
1 sibling, 2 replies; 43+ messages in thread
From: Merlin Moncure @ 2011-04-27 01:04 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Sok Ann Yap <sokann@gmail.com>; pgsql-performance
On Tue, Apr 26, 2011 at 4:37 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap <sokann@gmail.com> wrote:
>
>> So, index scan wins by a very small margin over sequential scan
>> after the tuning. I am a bit puzzled because index scan is more
>> than 3000 times faster in this case, but the estimated costs are
>> about the same. Did I do something wrong?
>
> Tuning is generally needed to get best performance from PostgreSQL.
> Needing to reduce random_page_cost is not unusual in situations
> where a good portion of the active data is in cache (between
> shared_buffers and the OS cache). Please show us your overall
> configuration and give a description of the hardware (how many of
> what kind of cores, how much RAM, what sort of storage system). The
> configuration part can be obtained by running the query on this page
> and pasting the result into your next post:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> There are probably some other configuration adjustments you could do
> to ensure that good plans are chosen.
The very first thing to check is effective_cache_size and to set it to
a reasonable value.
merlin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
@ 2011-04-27 07:22 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-27 07:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Claudio Freire <klaussfreire@gmail.com>
1 sibling, 1 reply; 43+ messages in thread
From: Claudio Freire @ 2011-04-27 07:22 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Sok Ann Yap <sokann@gmail.com>; pgsql-performance
On Wed, Apr 27, 2011 at 3:04 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> The very first thing to check is effective_cache_size and to set it to
> a reasonable value.
>
The problem there, I think, is that the planner is doing a full join,
instead of a semi-join.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-04-27 07:22 ` Re: reducing random_page_cost from 4 to 2 to force index scan Claudio Freire <klaussfreire@gmail.com>
@ 2011-04-27 07:23 ` Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 43+ messages in thread
From: Claudio Freire @ 2011-04-27 07:23 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Sok Ann Yap <sokann@gmail.com>; pgsql-performance
On Wed, Apr 27, 2011 at 9:22 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
> The problem there, I think, is that the planner is doing a full join,
> instead of a semi-join.
Or, rather, computing cost as if it was a full join. I'm not sure why.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
@ 2011-05-13 17:44 ` Robert Haas <robertmhaas@gmail.com>
2011-05-13 17:54 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 43+ messages in thread
From: Robert Haas @ 2011-05-13 17:44 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Sok Ann Yap <sokann@gmail.com>; pgsql-performance
On Tue, Apr 26, 2011 at 9:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Apr 26, 2011 at 4:37 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Sok Ann Yap <sokann@gmail.com> wrote:
>>
>>> So, index scan wins by a very small margin over sequential scan
>>> after the tuning. I am a bit puzzled because index scan is more
>>> than 3000 times faster in this case, but the estimated costs are
>>> about the same. Did I do something wrong?
>>
>> Tuning is generally needed to get best performance from PostgreSQL.
>> Needing to reduce random_page_cost is not unusual in situations
>> where a good portion of the active data is in cache (between
>> shared_buffers and the OS cache). Please show us your overall
>> configuration and give a description of the hardware (how many of
>> what kind of cores, how much RAM, what sort of storage system). The
>> configuration part can be obtained by running the query on this page
>> and pasting the result into your next post:
>>
>> http://wiki.postgresql.org/wiki/Server_Configuration
>>
>> There are probably some other configuration adjustments you could do
>> to ensure that good plans are chosen.
>
> The very first thing to check is effective_cache_size and to set it to
> a reasonable value.
Actually, effective_cache_size has no impact on costing except when
planning a nested loop with inner index scan. So, a query against a
single table can never benefit from changing that setting. Kevin's
suggestion of adjusting seq_page_cost and random_page_cost is the way
to go.
We've talked in the past (and I still think it's a good idea, but
haven't gotten around to doing anything about it) about adjusting the
planner to attribute to each relation the percentage of its pages
which we believe we'll find in cache. Although many complicated ideas
for determining that percentage have been proposed, my favorite one is
fairly simple: assume that small relations will be mostly or entirely
cached, and that big ones won't be. Allow the administrator to
override the result on a per-relation basis. It's difficult to
imagine a situation where the planner should assume that a relation
with only handful of pages isn't going to be cached. Even if it
isn't, as soon as someone begins accessing it, it will be.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-13 17:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 43+ messages in thread
From: Kevin Grittner @ 2011-05-13 17:54 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: Sok Ann Yap <sokann@gmail.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> wrote:
> We've talked in the past (and I still think it's a good idea, but
> haven't gotten around to doing anything about it) about adjusting
> the planner to attribute to each relation the percentage of its
> pages which we believe we'll find in cache. Although many
> complicated ideas for determining that percentage have been
> proposed, my favorite one is fairly simple: assume that small
> relations will be mostly or entirely cached, and that big ones
> won't be. Allow the administrator to override the result on a
> per-relation basis. It's difficult to imagine a situation where
> the planner should assume that a relation with only handful of
> pages isn't going to be cached. Even if it isn't, as soon as
> someone begins accessing it, it will be.
Simple as the heuristic is, I bet it would be effective. While one
can easily construct a synthetic case where it falls down, the ones
I can think of aren't all that common, and you are suggesting an
override mechanism.
-Kevin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-13 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
1 sibling, 1 reply; 43+ messages in thread
From: Tom Lane @ 2011-05-13 19:20 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Sok Ann Yap <sokann@gmail.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Apr 26, 2011 at 9:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> The very first thing to check is effective_cache_size and to set it to
>> a reasonable value.
> Actually, effective_cache_size has no impact on costing except when
> planning a nested loop with inner index scan. So, a query against a
> single table can never benefit from changing that setting.
That's flat out wrong. It does affect the cost estimate for plain
indexscan (and bitmap indexscan) plans.
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-05-13 20:04 ` Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-13 20:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Sok Ann Yap <sokann@gmail.com>; pgsql-performance
On Fri, May 13, 2011 at 3:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Apr 26, 2011 at 9:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>> The very first thing to check is effective_cache_size and to set it to
>>> a reasonable value.
>
>> Actually, effective_cache_size has no impact on costing except when
>> planning a nested loop with inner index scan. So, a query against a
>> single table can never benefit from changing that setting.
>
> That's flat out wrong. It does affect the cost estimate for plain
> indexscan (and bitmap indexscan) plans.
<rereads code>
OK, I agree. I obviously misinterpreted this code the last time I read it.
I guess maybe the reason why it didn't matter for the OP is that - if
the size of the index page in pages is smaller than the pro-rated
fraction of effective_cache_size allowed to the index - then the exact
value doesn't affect the answer.
I apparently need to study this code more.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-13 20:13 ` Josh Berkus <josh@agliodbs.com>
2011-05-13 20:51 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-14 02:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 3 replies; 43+ messages in thread
From: Josh Berkus @ 2011-05-13 20:13 UTC (permalink / raw)
To: pgsql-performance
> I guess maybe the reason why it didn't matter for the OP is that - if
> the size of the index page in pages is smaller than the pro-rated
> fraction of effective_cache_size allowed to the index - then the exact
> value doesn't affect the answer.
>
> I apparently need to study this code more.
FWIW: random_page_cost is meant to be the ratio between the cost of
looking up a single row as and index lookup, and the cost of looking up
that same row as part of a larger sequential scan. For specific
storage, that coefficient should be roughly the same regardless of the
table size. So if your plan for optimization involves manipulating RPC
for anything other than a change of storage, you're Doing It Wrong.
Instead, we should be fixing the formulas these are based on and leaving
RPC alone.
For any data page, there are actually four costs associated with each
tuple lookup, per:
in-memory/seq | on disk/seq
----------------+----------------
in-memory/random| on disk/random
(yes, there's actually more for bitmapscan etc. but the example holds)
For any given tuple lookup, then, you can assign a cost based on where
you think that tuple falls in that quadrant map. Since this is all
probability-based, you'd be assigning a cost as a mixed % of in-memory
and on-disk costs. Improvements in accuracy of this formula would come
through improvements in accuracy in predicting if a particular data page
will be in memory.
This is what the combination of random_page_cost and
effective_cache_size ought to supply, but I don't think it does, quite.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-13 20:51 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2 siblings, 0 replies; 43+ messages in thread
From: Cédric Villemain @ 2011-05-13 20:51 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
2011/5/13 Josh Berkus <josh@agliodbs.com>:
>
>> I guess maybe the reason why it didn't matter for the OP is that - if
>> the size of the index page in pages is smaller than the pro-rated
>> fraction of effective_cache_size allowed to the index - then the exact
>> value doesn't affect the answer.
>>
>> I apparently need to study this code more.
>
> FWIW: random_page_cost is meant to be the ratio between the cost of
> looking up a single row as and index lookup, and the cost of looking up
> that same row as part of a larger sequential scan. For specific
> storage, that coefficient should be roughly the same regardless of the
> table size. So if your plan for optimization involves manipulating RPC
> for anything other than a change of storage, you're Doing It Wrong.
>
> Instead, we should be fixing the formulas these are based on and leaving
> RPC alone.
>
> For any data page, there are actually four costs associated with each
> tuple lookup, per:
>
> in-memory/seq | on disk/seq
> ----------------+----------------
> in-memory/random| on disk/random
it lacks some more theorical like sort_page/temp_page : those are
based on a ratio of seq_page_cost and random_page_cost or a simple
seq_page_cost (when working out of work_mem)
memory access is accounted with some 0.1 in some place AFAIR.
(and memory random/seq is the same at the level of estimations we do)
>
> (yes, there's actually more for bitmapscan etc. but the example holds)
(if I read correctly the sources, for this one there is a linear
approach to ponderate the cost between random_page cost and
seq_page_cost on the heap page fetch plus the Mackert and Lohman
formula, if needed, in its best usage : predicting what should be in
cache *because* of the current query execution, not because of the
current status of the page cache)
>
> For any given tuple lookup, then, you can assign a cost based on where
> you think that tuple falls in that quadrant map. Since this is all
> probability-based, you'd be assigning a cost as a mixed % of in-memory
> and on-disk costs. Improvements in accuracy of this formula would come
> through improvements in accuracy in predicting if a particular data page
> will be in memory.
>
> This is what the combination of random_page_cost and
> effective_cache_size ought to supply, but I don't think it does, quite.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-14 02:20 ` Robert Haas <robertmhaas@gmail.com>
2011-05-15 18:08 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-14 02:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Fri, May 13, 2011 at 4:13 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Instead, we should be fixing the formulas these are based on and leaving
> RPC alone.
>
> For any data page, there are actually four costs associated with each
> tuple lookup, per:
All true. I suspect that in practice the different between random and
sequential memory page costs is small enough to be ignorable, although
of course I might be wrong. I've never seen a database that was fully
cached in memory where it was necessary to set
random_page_cost>seq_page_cost to get good plans -- no doubt partly
because even if the pages were consecutive on disk, there's no reason
to suppose they would be so in memory, and we certainly wouldn't know
one way or the other at planning time. But I agree we should add a
cached_page_cost as part of all this.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-14 02:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-15 18:08 ` Josh Berkus <josh@agliodbs.com>
2011-05-15 20:32 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2011-05-15 18:08 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
Robert,
> All true. I suspect that in practice the different between random and
> sequential memory page costs is small enough to be ignorable, although
> of course I might be wrong.
This hasn't been my experience, although I have not carefully measured
it. In fact, there's good reason to suppose that, if you were selecting
50% of more of a table, sequential access would still be faster even for
an entirely in-memory table.
As a parallel to our development, Redis used to store all data as linked
lists, making every object lookup effectively a random lookup. They
found that even with a database which is pinned in memory, creating a
data page structure (they call it "ziplists") and supporting sequential
scans was up to 10X faster for large lists.
So I would assume that there is still a coefficient difference between
seeks and scans in memory until proven otherwise.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-14 02:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-15 18:08 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-15 20:32 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 43+ messages in thread
From: Robert Haas @ 2011-05-15 20:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Sun, May 15, 2011 at 2:08 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> All true. I suspect that in practice the different between random and
>> sequential memory page costs is small enough to be ignorable, although
>> of course I might be wrong.
>
> This hasn't been my experience, although I have not carefully measured
> it. In fact, there's good reason to suppose that, if you were selecting
> 50% of more of a table, sequential access would still be faster even for
> an entirely in-memory table.
>
> As a parallel to our development, Redis used to store all data as linked
> lists, making every object lookup effectively a random lookup. They
> found that even with a database which is pinned in memory, creating a
> data page structure (they call it "ziplists") and supporting sequential
> scans was up to 10X faster for large lists.
>
> So I would assume that there is still a coefficient difference between
> seeks and scans in memory until proven otherwise.
Well, anything's possible. But I wonder whether the effects you are
describing might result from a reduction in the *number* of pages
accessed rather than a change in the access pattern.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-15 03:49 ` Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 43+ messages in thread
From: Stuart Bishop @ 2011-05-15 03:49 UTC (permalink / raw)
To: pgsql-performance
On Sat, May 14, 2011 at 3:13 AM, Josh Berkus <josh@agliodbs.com> wrote:
> This is what the combination of random_page_cost and
> effective_cache_size ought to supply, but I don't think it does, quite.
I think random_page_cost causes problems because I need to combine
disk random access time, which I can measure, with a guesstimate of
the disk cache hit rate. It would be lovely if these two variables
were separate. It would be even lovelier if the disk cache hit rate
could be probed at run time and didn't need setting at all, but I
suspect that isn't possible on some platforms.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
@ 2011-05-15 18:20 ` Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2011-05-15 18:20 UTC (permalink / raw)
To: pgsql-performance
Stuart,
> I think random_page_cost causes problems because I need to combine
> disk random access time, which I can measure, with a guesstimate of
> the disk cache hit rate.
See, that's wrong. Disk cache hit rate is what effective_cache_size
(ECS) is for.
Really, there's several factors which should be going into the planner's
estimates to determine a probability of a table being cached:
* ratio between total database size and ECS
* ratio between table size and ECS
* ratio between index size and ECS
* whether the table is "hot" or not
* whether the index is "hot" or not
The last two statistics are critically important for good estimation,
and they are not things we currently collect. By "hot" I mean: is this
a relation which is accessed several times per minute/hour and is thus
likely to be in the cache when we need it? Currently, we have no way of
knowing that.
Without "hot" statistics, we're left with guessing based on size, which
results in bad plans for small tables in large databases which are
accessed infrequently.
Mind you, for large tables it would be even better to go beyond that and
actually have some knowledge of which disk pages might be in cache.
However, I think that's beyond feasibility for current software/OSes.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-15 21:45 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 43+ messages in thread
From: Cédric Villemain @ 2011-05-15 21:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
2011/5/15 Josh Berkus <josh@agliodbs.com>:
> Stuart,
>
>> I think random_page_cost causes problems because I need to combine
>> disk random access time, which I can measure, with a guesstimate of
>> the disk cache hit rate.
>
> See, that's wrong. Disk cache hit rate is what effective_cache_size
> (ECS) is for.
>
> Really, there's several factors which should be going into the planner's
> estimates to determine a probability of a table being cached:
>
> * ratio between total database size and ECS
> * ratio between table size and ECS
> * ratio between index size and ECS
> * whether the table is "hot" or not
> * whether the index is "hot" or not
>
> The last two statistics are critically important for good estimation,
> and they are not things we currently collect. By "hot" I mean: is this
> a relation which is accessed several times per minute/hour and is thus
> likely to be in the cache when we need it? Currently, we have no way of
> knowing that.
>
> Without "hot" statistics, we're left with guessing based on size, which
> results in bad plans for small tables in large databases which are
> accessed infrequently.
>
> Mind you, for large tables it would be even better to go beyond that and
> actually have some knowledge of which
*which* ?
do you mean 'area' of the tables ?
> disk pages might be in cache.
> However, I think that's beyond feasibility for current software/OSes.
maybe not :) mincore is available in many OSes, and windows have
options to get those stats too.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
@ 2011-05-16 01:05 ` Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 43+ messages in thread
From: Craig Ringer @ 2011-05-16 01:05 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
On 16/05/11 05:45, Cédric Villemain wrote:
> 2011/5/15 Josh Berkus <josh@agliodbs.com>:
>> disk pages might be in cache.
>> However, I think that's beyond feasibility for current software/OSes.
>
> maybe not :) mincore is available in many OSes, and windows have
> options to get those stats too.
AFAIK, mincore() is only useful for mmap()ed files and for finding out
if it's safe to access certain blocks of memory w/o risking triggering
heavy swapping.
It doesn't provide any visibility into the OS's block device / file
system caches; you can't ask it "how much of this file is cached in RAM"
or "is this range of blocks in this file cached in RAM".
Even if you could, it's hard to see how an approach that relied on
asking the OS through system calls about the cache state when planning
every query could be fast enough to be viable.
--
Craig Ringer
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
@ 2011-05-16 01:18 ` Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 43+ messages in thread
From: Greg Smith @ 2011-05-16 01:18 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Cédric Villemain <cedric.villemain.debian@gmail.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
Craig Ringer wrote:
> AFAIK, mincore() is only useful for mmap()ed files and for finding out
> if it's safe to access certain blocks of memory w/o risking triggering
> heavy swapping.
>
> It doesn't provide any visibility into the OS's block device / file
> system caches; you can't ask it "how much of this file is cached in RAM"
> or "is this range of blocks in this file cached in RAM".
>
You should try out pgfincore if you think this can't be done!
> Even if you could, it's hard to see how an approach that relied on
> asking the OS through system calls about the cache state when planning
> every query could be fast enough to be viable.
>
You can't do it in real-time. You don't necessarily want that to even
if it were possible; too many possibilities for nasty feedback loops
where you always favor using some marginal index that happens to be in
memory, and therefore never page in things that would be faster once
they're read. The only reasonable implementation that avoids completely
unstable plans is to scan this data periodically and save some
statistics on it--the way ANALYZE does--and then have that turn into a
planner input.
The related secondary idea of just making assumptions about small
tables/indexes, too, may be a useful heuristic to layer on top of this.
There's a pile of ideas here that all seem reasonable both in terms of
modeling real-world behavior and as things that could be inserted into
the optimizer. As usual, I suspect that work is needs to be followed by
a giant testing exercise though.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
@ 2011-05-16 04:41 ` Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 43+ messages in thread
From: Jesper Krogh @ 2011-05-16 04:41 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; Cédric Villemain <cedric.villemain.debian@gmail.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
On 2011-05-16 03:18, Greg Smith wrote:
> You can't do it in real-time. You don't necessarily want that to
> even if it were possible; too many possibilities for nasty feedback
> loops where you always favor using some marginal index that happens
> to be in memory, and therefore never page in things that would be
> faster once they're read. The only reasonable implementation that
> avoids completely unstable plans is to scan this data periodically
> and save some statistics on it--the way ANALYZE does--and then have
> that turn into a planner input.
Would that be feasible? Have process collecting the data every now-and-then
probably picking some conservative-average function and feeding
it into pg_stats for each index/relation?
To me it seems like a robust and fairly trivial way to to get better
numbers. The
fear is that the OS-cache is too much in flux to get any stable numbers out
of it.
--
Jesper
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
@ 2011-05-16 04:49 ` Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 16:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 43+ messages in thread
From: Jesper Krogh @ 2011-05-16 04:49 UTC (permalink / raw)
To: pgsql-performance
On 2011-05-16 06:41, Jesper Krogh wrote:
> On 2011-05-16 03:18, Greg Smith wrote:
>> You can't do it in real-time. You don't necessarily want that to
>> even if it were possible; too many possibilities for nasty feedback
>> loops where you always favor using some marginal index that happens
>> to be in memory, and therefore never page in things that would be
>> faster once they're read. The only reasonable implementation that
>> avoids completely unstable plans is to scan this data periodically
>> and save some statistics on it--the way ANALYZE does--and then have
>> that turn into a planner input.
>
> Would that be feasible? Have process collecting the data every
> now-and-then
> probably picking some conservative-average function and feeding
> it into pg_stats for each index/relation?
>
> To me it seems like a robust and fairly trivial way to to get better
> numbers. The
> fear is that the OS-cache is too much in flux to get any stable
> numbers out
> of it.
Ok, it may not work as well with index'es, since having 1% in cache may very
well mean that 90% of all requested blocks are there.. for tables in should
be more trivial.
--
Jesper
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
@ 2011-05-16 14:34 ` Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-16 14:34 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-performance
On Mon, May 16, 2011 at 12:49 AM, Jesper Krogh <jesper@krogh.cc> wrote:
>> To me it seems like a robust and fairly trivial way to to get better
>> numbers. The
>> fear is that the OS-cache is too much in flux to get any stable numbers
>> out
>> of it.
>
> Ok, it may not work as well with index'es, since having 1% in cache may very
> well mean that 90% of all requested blocks are there.. for tables in should
> be more trivial.
Tables can have hot spots, too. Consider a table that holds calendar
reservations. Reservations can be inserted, updated, deleted. But
typically, the most recent data will be what is most actively
modified, and the older data will be relatively more (though not
completely) static, and less frequently accessed. Such examples are
common in many real-world applications.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-16 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
0 siblings, 1 reply; 43+ messages in thread
From: Tom Lane @ 2011-05-16 15:46 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Jesper Krogh <jesper@krogh.cc>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, May 16, 2011 at 12:49 AM, Jesper Krogh <jesper@krogh.cc> wrote:
>> Ok, it may not work as well with index'es, since having 1% in cache may very
>> well mean that 90% of all requested blocks are there.. for tables in should
>> be more trivial.
> Tables can have hot spots, too. Consider a table that holds calendar
> reservations. Reservations can be inserted, updated, deleted. But
> typically, the most recent data will be what is most actively
> modified, and the older data will be relatively more (though not
> completely) static, and less frequently accessed. Such examples are
> common in many real-world applications.
Yes. I'm not convinced that measuring the fraction of a table or index
that's in cache is really going to help us much. Historical cache hit
rates might be useful, but only to the extent that the incoming query
has a similar access pattern to those in the (recent?) past. It's not
an easy problem.
I almost wonder if we should not try to measure this at all, but instead
let the DBA set a per-table or per-index number to use, analogous to the
override we added recently for column n-distinct statistics ...
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-05-17 19:19 ` Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 43+ messages in thread
From: Jim Nasby @ 2011-05-17 19:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
On May 16, 2011, at 10:46 AM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, May 16, 2011 at 12:49 AM, Jesper Krogh <jesper@krogh.cc> wrote:
>>> Ok, it may not work as well with index'es, since having 1% in cache may very
>>> well mean that 90% of all requested blocks are there.. for tables in should
>>> be more trivial.
>
>> Tables can have hot spots, too. Consider a table that holds calendar
>> reservations. Reservations can be inserted, updated, deleted. But
>> typically, the most recent data will be what is most actively
>> modified, and the older data will be relatively more (though not
>> completely) static, and less frequently accessed. Such examples are
>> common in many real-world applications.
>
> Yes. I'm not convinced that measuring the fraction of a table or index
> that's in cache is really going to help us much. Historical cache hit
> rates might be useful, but only to the extent that the incoming query
> has a similar access pattern to those in the (recent?) past. It's not
> an easy problem.
>
> I almost wonder if we should not try to measure this at all, but instead
> let the DBA set a per-table or per-index number to use, analogous to the
> override we added recently for column n-distinct statistics ...
I think the challenge there would be how to define the scope of the hot-spot. Is it the last X pages? Last X serial values? Something like correlation?
Hmm... it would be interesting if we had average relation access times for each stats bucket on a per-column basis; that would give the planner a better idea of how much IO overhead there would be for a given WHERE clause.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
@ 2011-05-19 03:00 ` Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Greg Smith @ 2011-05-19 03:00 UTC (permalink / raw)
To: Jim Nasby <jim@nasby.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
Jim Nasby wrote:
> I think the challenge there would be how to define the scope of the hot-spot. Is it the last X pages? Last X serial values? Something like correlation?
>
> Hmm... it would be interesting if we had average relation access times for each stats bucket on a per-column basis; that would give the planner a better idea of how much IO overhead there would be for a given WHERE clause
You've already given one reasonable first answer to your question here.
If you defined a usage counter for each histogram bucket, and
incremented that each time something from it was touched, that could
lead to a very rough way to determine access distribution. Compute a
ratio of the counts in those buckets, then have an estimate of the total
cached percentage; multiplying the two will give you an idea how much of
that specific bucket might be in memory. It's not perfect, and you need
to incorporate some sort of aging method to it (probably weighted
average based), but the basic idea could work.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
@ 2011-05-19 14:53 ` Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
0 siblings, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-19 14:53 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Jim Nasby <jim@nasby.net>; Tom Lane <tgl@sss.pgh.pa.us>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
On Wed, May 18, 2011 at 11:00 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Jim Nasby wrote:
>> I think the challenge there would be how to define the scope of the
>> hot-spot. Is it the last X pages? Last X serial values? Something like
>> correlation?
>>
>> Hmm... it would be interesting if we had average relation access times for
>> each stats bucket on a per-column basis; that would give the planner a
>> better idea of how much IO overhead there would be for a given WHERE clause
>
> You've already given one reasonable first answer to your question here. If
> you defined a usage counter for each histogram bucket, and incremented that
> each time something from it was touched, that could lead to a very rough way
> to determine access distribution. Compute a ratio of the counts in those
> buckets, then have an estimate of the total cached percentage; multiplying
> the two will give you an idea how much of that specific bucket might be in
> memory. It's not perfect, and you need to incorporate some sort of aging
> method to it (probably weighted average based), but the basic idea could
> work.
Maybe I'm missing something here, but it seems like that would be
nightmarishly slow. Every time you read a tuple, you'd have to look
at every column of the tuple and determine which histogram bucket it
was in (or, presumably, which MCV it is, since those aren't included
in working out the histogram buckets). That seems like it would slow
down a sequential scan by at least 10x.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-19 18:39 ` Jim Nasby <jim@nasby.net>
2011-05-19 20:07 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 22:27 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 2 replies; 43+ messages in thread
From: Jim Nasby @ 2011-05-19 18:39 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
On May 19, 2011, at 9:53 AM, Robert Haas wrote:
> On Wed, May 18, 2011 at 11:00 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> Jim Nasby wrote:
>>> I think the challenge there would be how to define the scope of the
>>> hot-spot. Is it the last X pages? Last X serial values? Something like
>>> correlation?
>>>
>>> Hmm... it would be interesting if we had average relation access times for
>>> each stats bucket on a per-column basis; that would give the planner a
>>> better idea of how much IO overhead there would be for a given WHERE clause
>>
>> You've already given one reasonable first answer to your question here. If
>> you defined a usage counter for each histogram bucket, and incremented that
>> each time something from it was touched, that could lead to a very rough way
>> to determine access distribution. Compute a ratio of the counts in those
>> buckets, then have an estimate of the total cached percentage; multiplying
>> the two will give you an idea how much of that specific bucket might be in
>> memory. It's not perfect, and you need to incorporate some sort of aging
>> method to it (probably weighted average based), but the basic idea could
>> work.
>
> Maybe I'm missing something here, but it seems like that would be
> nightmarishly slow. Every time you read a tuple, you'd have to look
> at every column of the tuple and determine which histogram bucket it
> was in (or, presumably, which MCV it is, since those aren't included
> in working out the histogram buckets). That seems like it would slow
> down a sequential scan by at least 10x.
You definitely couldn't do it real-time. But you might be able to copy the tuple somewhere and have a background process do the analysis.
That said, it might be more productive to know what blocks are available in memory and use correlation to guesstimate whether a particular query will need hot or cold blocks. Or perhaps we create a different structure that lets you track the distribution of each column linearly through the table; something more sophisticated than just using correlation.... perhaps something like indicating which stats bucket was most prevalent in each block/range of blocks in a table. That information would allow you to estimate exactly what blocks in the table you're likely to need...
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
@ 2011-05-19 20:07 ` Robert Haas <robertmhaas@gmail.com>
2011-05-23 19:08 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-19 20:07 UTC (permalink / raw)
To: Jim Nasby <jim@nasby.net>; +Cc: Greg Smith <greg@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
On Thu, May 19, 2011 at 2:39 PM, Jim Nasby <jim@nasby.net> wrote:
> On May 19, 2011, at 9:53 AM, Robert Haas wrote:
>> On Wed, May 18, 2011 at 11:00 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>>> Jim Nasby wrote:
>>>> I think the challenge there would be how to define the scope of the
>>>> hot-spot. Is it the last X pages? Last X serial values? Something like
>>>> correlation?
>>>>
>>>> Hmm... it would be interesting if we had average relation access times for
>>>> each stats bucket on a per-column basis; that would give the planner a
>>>> better idea of how much IO overhead there would be for a given WHERE clause
>>>
>>> You've already given one reasonable first answer to your question here. If
>>> you defined a usage counter for each histogram bucket, and incremented that
>>> each time something from it was touched, that could lead to a very rough way
>>> to determine access distribution. Compute a ratio of the counts in those
>>> buckets, then have an estimate of the total cached percentage; multiplying
>>> the two will give you an idea how much of that specific bucket might be in
>>> memory. It's not perfect, and you need to incorporate some sort of aging
>>> method to it (probably weighted average based), but the basic idea could
>>> work.
>>
>> Maybe I'm missing something here, but it seems like that would be
>> nightmarishly slow. Every time you read a tuple, you'd have to look
>> at every column of the tuple and determine which histogram bucket it
>> was in (or, presumably, which MCV it is, since those aren't included
>> in working out the histogram buckets). That seems like it would slow
>> down a sequential scan by at least 10x.
>
> You definitely couldn't do it real-time. But you might be able to copy the tuple somewhere and have a background process do the analysis.
>
> That said, it might be more productive to know what blocks are available in memory and use correlation to guesstimate whether a particular query will need hot or cold blocks. Or perhaps we create a different structure that lets you track the distribution of each column linearly through the table; something more sophisticated than just using correlation.... perhaps something like indicating which stats bucket was most prevalent in each block/range of blocks in a table. That information would allow you to estimate exactly what blocks in the table you're likely to need...
Well, all of that stuff sounds impractically expensive to me... but I
just work here.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 20:07 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-23 19:08 ` Josh Berkus <josh@agliodbs.com>
2011-05-24 02:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2011-05-23 19:08 UTC (permalink / raw)
To: pgsql-performance
> Well, all of that stuff sounds impractically expensive to me... but I
> just work here.
I'll point out that the simple version, which just checks for hot tables
and indexes, would improve estimates greatly and be a LOT less
complicated than these proposals. Certainly having some form of
block-based or range-based stats would be better, but it also sounds
hard enough to maybe never get done.
Having user-accessible "hot" stats would also be useful to DBAs.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 20:07 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-23 19:08 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
@ 2011-05-24 02:44 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 09:12 ` Re: reducing random_page_cost from 4 to 2 to force index scan Vitalii Tymchyshyn <tivv00@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Robert Haas @ 2011-05-24 02:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Mon, May 23, 2011 at 3:08 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> Well, all of that stuff sounds impractically expensive to me... but I
>> just work here.
>
> I'll point out that the simple version, which just checks for hot tables
> and indexes, would improve estimates greatly and be a LOT less
> complicated than these proposals.
I realize I'm sounding like a broken record here, but as far as I can
tell there is absolutely zero evidence that that would be better. I'm
sure you're in good company thinking so, but the list of things that
could skew (or should I say, screw) the estimates is long and painful;
and if those estimates are wrong, you'll end up with something that is
both worse and less predictable than the status quo. First, I haven't
seen a shred of hard evidence that the contents of the buffer cache or
OS cache are stable enough to be relied upon, and we've repeatedly
discussed workloads where that might not be true. Has anyone done a
systematic study of this on a variety real production systems? If so,
the results haven't been posted here, at least not that I can recall.
Second, even if we were willing to accept that we could obtain
relatively stable and accurate measurements of this data, who is to
say that basing plans on it would actually result in an improvement in
plan quality? That may seem obvious, but I don't think it is. The
proposed method is a bit like trying to determine the altitude of a
hot air balloon by throwing the ballast over the side and timing how
long it takes to hit the ground. Executing plans that are based on
the contents of the cache will change the contents of the cache, which
will in turn change the plans. The idea that we can know, without any
experimentation, how that's going to shake out, seems to me to be an
exercise in unjustified optimism of the first order.
Sorry to sound grumpy and pessimistic, but I really think we're
letting our enthusiasm get way, way ahead of the evidence.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 20:07 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-23 19:08 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-24 02:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
@ 2011-05-24 09:12 ` Vitalii Tymchyshyn <tivv00@gmail.com>
0 siblings, 0 replies; 43+ messages in thread
From: Vitalii Tymchyshyn @ 2011-05-24 09:12 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Hello.
As of me, all this "hot" thing really looks like uncertain and dynamic
enough.
Two things that I could directly use right now (and they are needed in
pair) are:
1)Per-table/index/database bufferpools (split shared buffer into parts,
allow to specify which index/table/database goes where)
2)Per-table/index cost settings
If I had this, I could allocate specific bufferpools for tables/indexes
that MUST be hot in memory and set low costs for this specific tables.
P.S. Third thing, great to have to companion this two is "Load on
startup" flag to automatically populate bufferpools with fast sequential
read, but this can be easily emulated with a statement.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jim Nasby <jim@nasby.net>
@ 2011-05-19 22:27 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
1 sibling, 0 replies; 43+ messages in thread
From: Cédric Villemain @ 2011-05-19 22:27 UTC (permalink / raw)
To: Jim Nasby <jim@nasby.net>; +Cc: Robert Haas <robertmhaas@gmail.com>; Greg Smith <greg@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
2011/5/19 Jim Nasby <jim@nasby.net>:
> On May 19, 2011, at 9:53 AM, Robert Haas wrote:
>> On Wed, May 18, 2011 at 11:00 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>>> Jim Nasby wrote:
>>>> I think the challenge there would be how to define the scope of the
>>>> hot-spot. Is it the last X pages? Last X serial values? Something like
>>>> correlation?
>>>>
>>>> Hmm... it would be interesting if we had average relation access times for
>>>> each stats bucket on a per-column basis; that would give the planner a
>>>> better idea of how much IO overhead there would be for a given WHERE clause
>>>
>>> You've already given one reasonable first answer to your question here. If
>>> you defined a usage counter for each histogram bucket, and incremented that
>>> each time something from it was touched, that could lead to a very rough way
>>> to determine access distribution. Compute a ratio of the counts in those
>>> buckets, then have an estimate of the total cached percentage; multiplying
>>> the two will give you an idea how much of that specific bucket might be in
>>> memory. It's not perfect, and you need to incorporate some sort of aging
>>> method to it (probably weighted average based), but the basic idea could
>>> work.
>>
>> Maybe I'm missing something here, but it seems like that would be
>> nightmarishly slow. Every time you read a tuple, you'd have to look
>> at every column of the tuple and determine which histogram bucket it
>> was in (or, presumably, which MCV it is, since those aren't included
>> in working out the histogram buckets). That seems like it would slow
>> down a sequential scan by at least 10x.
>
> You definitely couldn't do it real-time. But you might be able to copy the tuple somewhere and have a background process do the analysis.
>
> That said, it might be more productive to know what blocks are available in memory and use correlation to guesstimate whether a particular query will need hot or cold blocks. Or perhaps we create a different structure that lets you track the distribution of each column linearly through the table; something more sophisticated than just using correlation.... perhaps something like indicating which stats bucket was most prevalent in each block/range of blocks in a table. That information would allow you to estimate exactly what blocks in the table you're likely to need...
Those are very good ideas I would get in mind for vacuum/checkpoint
tasks: if you are able to know hot and cold data, then order it in the
segments of the relation. But making it work at the planner level
looks hard. I am not opposed to the idea, but no idea how to do it
right now.
> --
> Jim C. Nasby, Database Architect jim@nasby.net
> 512.569.9461 (cell) http://jim.nasby.net
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
@ 2011-05-16 16:45 ` Jeff Janes <jeff.janes@gmail.com>
2011-05-16 17:24 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 43+ messages in thread
From: Jeff Janes @ 2011-05-16 16:45 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-performance
On Sun, May 15, 2011 at 9:49 PM, Jesper Krogh <jesper@krogh.cc> wrote:
>
> Ok, it may not work as well with index'es, since having 1% in cache may very
> well mean that 90% of all requested blocks are there.. for tables in should
> be more trivial.
Why would the index have a meaningful hot-spot unless the underlying
table had one as well? (Of course the root block will be a hot-spot,
but certainly not 90% of all requests)
Cheers,
Jeff
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 16:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jeff Janes <jeff.janes@gmail.com>
@ 2011-05-16 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-16 19:10 ` Re: reducing random_page_cost from 4 to 2 to force index scan Nathan Boley <npboley@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Tom Lane @ 2011-05-16 17:24 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Jesper Krogh <jesper@krogh.cc>; pgsql-performance
Jeff Janes <jeff.janes@gmail.com> writes:
> On Sun, May 15, 2011 at 9:49 PM, Jesper Krogh <jesper@krogh.cc> wrote:
>> Ok, it may not work as well with index'es, since having 1% in cache may very
>> well mean that 90% of all requested blocks are there.. for tables in should
>> be more trivial.
> Why would the index have a meaningful hot-spot unless the underlying
> table had one as well? (Of course the root block will be a hot-spot,
> but certainly not 90% of all requests)
The accesses to an index are far more likely to be clustered than the
accesses to the underlying table, because the index is organized in a
way that's application-meaningful and the table not so much. Continuing
the earlier example of a timestamp column, accesses might preferentially
hit near the right end of the index while the underlying rows are all
over the table.
IOW, hot spots measured at the row level and hot spots measured at the
page level could very easily be different between table and index.
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 16:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jeff Janes <jeff.janes@gmail.com>
2011-05-16 17:24 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-05-16 19:10 ` Nathan Boley <npboley@gmail.com>
2011-05-16 19:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 43+ messages in thread
From: Nathan Boley @ 2011-05-16 19:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
> The accesses to an index are far more likely to be clustered than the
> accesses to the underlying table, because the index is organized in a
> way that's application-meaningful and the table not so much.
So, to clarify, are you saying that if query were actually requesting
rows uniformly random, then there would be no reason to suspect that
index accesses would have hotspots? It seems like the index structure
( ie, the top node in b-trees ) could also get in the way.
Best,
Nathan
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 01:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Merlin Moncure <mmoncure@gmail.com>
2011-05-13 17:44 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 19:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Re: reducing random_page_cost from 4 to 2 to force index scan Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 03:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Re: reducing random_page_cost from 4 to 2 to force index scan Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Re: reducing random_page_cost from 4 to 2 to force index scan Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Re: reducing random_page_cost from 4 to 2 to force index scan Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jesper Krogh <jesper@krogh.cc>
2011-05-16 16:45 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jeff Janes <jeff.janes@gmail.com>
2011-05-16 17:24 ` Re: reducing random_page_cost from 4 to 2 to force index scan Tom Lane <tgl@sss.pgh.pa.us>
2011-05-16 19:10 ` Re: reducing random_page_cost from 4 to 2 to force index scan Nathan Boley <npboley@gmail.com>
@ 2011-05-16 19:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 43+ messages in thread
From: Tom Lane @ 2011-05-16 19:41 UTC (permalink / raw)
To: Nathan Boley <npboley@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Jesper Krogh <jesper@krogh.cc>; pgsql-performance
Nathan Boley <npboley@gmail.com> writes:
>> The accesses to an index are far more likely to be clustered than the
>> accesses to the underlying table, because the index is organized in a
>> way that's application-meaningful and the table not so much.
> So, to clarify, are you saying that if query were actually requesting
> rows uniformly random, then there would be no reason to suspect that
> index accesses would have hotspots? It seems like the index structure
> ( ie, the top node in b-trees ) could also get in the way.
The upper nodes would tend to stay in cache, yes, but we already assume
that in the index access cost model, in a kind of indirect way: the
model only considers leaf-page accesses in the first place ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
@ 2011-04-27 12:40 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Kevin Grittner @ 2011-04-27 12:40 UTC (permalink / raw)
To: sokann@gmail.com; +Cc: pgsql-performance
Sok Ann Yap wrote:
> Kevin Grittner wrote:
>> Please show us your overall configuration and give a description
>> of the hardware (how many of what kind of cores, how much RAM,
>> what sort of storage system).
> Here's the configuration (this is just a low end laptop):
> version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
> compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0,
> pie-0.4.5) 4.5.2, 64-bit
> checkpoint_segments | 16
> default_statistics_target | 10000
Usually overkill. If this didn't help, you should probably change it
back.
> effective_cache_size | 512MB
> lc_collate | en_US.UTF-8
> lc_ctype | en_US.UTF-8
> listen_addresses | *
> log_destination | syslog
> log_min_duration_statement | 0
> maintenance_work_mem | 256MB
> max_connections | 100
You probably don't need this many connections.
> max_stack_depth | 2MB
> port | 5432
> random_page_cost | 4
> server_encoding | UTF8
> shared_buffers | 256MB
> silent_mode | on
> TimeZone | Asia/Kuala_Lumpur
> wal_buffers | 1MB
> work_mem | 32MB
> (20 rows)
It's hard to recommend other changes without knowing the RAM on the
system. How many of what kind of CPUs would help, too.
> The thing is, the query I posted was fairly simple (I think), and
> PostgreSQL should be able to choose the 3000+ times faster index
> scan with the default random_page_cost of 4.
It picks the plan with the lowest estimated cost. If it's not
picking the best plan, that's usually an indication that you need to
adjust cost factors so that estimates better model the actual costs.
> If I need to reduce it to 2 when using a 5.4k rpm slow disk, what
> is random_page_cost = 4 good for?
It's good for large databases with a lot of physical disk I/O. In
fact, in some of those cases, it needs to be higher. In your test,
the numbers indicate that everything was cached in RAM. That makes
the effective cost very low.
Also, the odds are that you have more total cache space between the
shared_buffers and the OS cache than the effective_cache_size
setting, so the optimizer doesn't expect the number of cache hits
you're getting on index usage.
-Kevin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-27 22:34 ` Sok Ann Yap <sokann@gmail.com>
2011-04-27 23:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 43+ messages in thread
From: Sok Ann Yap @ 2011-04-27 22:34 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On Wed, Apr 27, 2011 at 8:40 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap wrote:
>> Kevin Grittner wrote:
>
>>> Please show us your overall configuration and give a description
>>> of the hardware (how many of what kind of cores, how much RAM,
>>> what sort of storage system).
>
>> Here's the configuration (this is just a low end laptop):
>
>> version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
>> compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0,
>> pie-0.4.5) 4.5.2, 64-bit
>> checkpoint_segments | 16
>> default_statistics_target | 10000
>
> Usually overkill. If this didn't help, you should probably change it
> back.
>
>> effective_cache_size | 512MB
>> lc_collate | en_US.UTF-8
>> lc_ctype | en_US.UTF-8
>> listen_addresses | *
>> log_destination | syslog
>> log_min_duration_statement | 0
>> maintenance_work_mem | 256MB
>> max_connections | 100
>
> You probably don't need this many connections.
>
>> max_stack_depth | 2MB
>> port | 5432
>> random_page_cost | 4
>> server_encoding | UTF8
>> shared_buffers | 256MB
>> silent_mode | on
>> TimeZone | Asia/Kuala_Lumpur
>> wal_buffers | 1MB
>> work_mem | 32MB
>> (20 rows)
>
> It's hard to recommend other changes without knowing the RAM on the
> system. How many of what kind of CPUs would help, too.
>
>> The thing is, the query I posted was fairly simple (I think), and
>> PostgreSQL should be able to choose the 3000+ times faster index
>> scan with the default random_page_cost of 4.
>
> It picks the plan with the lowest estimated cost. If it's not
> picking the best plan, that's usually an indication that you need to
> adjust cost factors so that estimates better model the actual costs.
>
>> If I need to reduce it to 2 when using a 5.4k rpm slow disk, what
>> is random_page_cost = 4 good for?
>
> It's good for large databases with a lot of physical disk I/O. In
> fact, in some of those cases, it needs to be higher. In your test,
> the numbers indicate that everything was cached in RAM. That makes
> the effective cost very low.
>
> Also, the odds are that you have more total cache space between the
> shared_buffers and the OS cache than the effective_cache_size
> setting, so the optimizer doesn't expect the number of cache hits
> you're getting on index usage.
>
> -Kevin
>
Thanks for the tips and explanation. I wrongly assumed the
random_page_cost value is independent from caching.
Now, let's go back to the original query:
SELECT
salutations.id,
salutations.name,
EXISTS (
SELECT 1
FROM contacts
WHERE salutations.id = contacts.salutation_id
) AS in_use
FROM salutations
If I split up the query, i.e. running this once:
SELECT
salutations.id,
salutations.name
FROM salutations
and then running this 44 times, once for each row:
SELECT
EXISTS (
SELECT 1
FROM contacts
WHERE contacts.salutation_id = ?
) AS in_use
I can see that PostgreSQL will smartly pick the best plan, i.e. for
common salutations (Madam, Ms, etc), it will do sequential scan, while
for salutations that are rarely used or not used at all, it will do
index scan.
Anyway, the overhead of spawning 44 extra queries means that it is
still better off for me to stick with the original query and tune
PostgreSQL to choose index scan.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
@ 2011-04-27 23:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-28 00:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
0 siblings, 1 reply; 43+ messages in thread
From: Kevin Grittner @ 2011-04-27 23:23 UTC (permalink / raw)
To: Sok Ann Yap <sokann@gmail.com>; +Cc: pgsql-performance
Sok Ann Yap <sokann@gmail.com> wrote:
> Anyway, the overhead of spawning 44 extra queries means that it is
> still better off for me to stick with the original query and tune
> PostgreSQL to choose index scan.
Maybe, but what is *best* for you is to tune PostgreSQL so that your
costs are accurately modeled, at which point it will automatically
pick the best plan for most or all of your queries without you
needing to worry about it.
If you set your effective_cache_size to the sum of shared_buffers
and what your OS reports as cache after you've been running a while,
that will help the optimizer know what size index fits in RAM, and
will tend to encourage index use. If the active portion of your
data is heavily cached, you might want to set random_page_cost and
seq_page_cost to the same value, and make that value somewhere in
the 0.1 to 0.05 range. If you have moderate caching, using 1 and 2
can be good.
If you're still not getting reasonable plans, please post again with
more information about your hardware along with the query and its
EXPLAIN ANALYZE output.
-Kevin
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-27 23:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-28 00:19 ` Sok Ann Yap <sokann@gmail.com>
2011-04-28 11:32 ` Re: reducing random_page_cost from 4 to 2 to force index scan Samuel Gendler <sgendler@ideasculptor.com>
2011-04-28 15:56 ` Re: reducing random_page_cost from 4 to 2 to force index scan Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 43+ messages in thread
From: Sok Ann Yap @ 2011-04-28 00:19 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On Thu, Apr 28, 2011 at 7:23 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap <sokann@gmail.com> wrote:
>
>> Anyway, the overhead of spawning 44 extra queries means that it is
>> still better off for me to stick with the original query and tune
>> PostgreSQL to choose index scan.
>
> Maybe, but what is *best* for you is to tune PostgreSQL so that your
> costs are accurately modeled, at which point it will automatically
> pick the best plan for most or all of your queries without you
> needing to worry about it.
>
> If you set your effective_cache_size to the sum of shared_buffers
> and what your OS reports as cache after you've been running a while,
> that will help the optimizer know what size index fits in RAM, and
> will tend to encourage index use. If the active portion of your
> data is heavily cached, you might want to set random_page_cost and
> seq_page_cost to the same value, and make that value somewhere in
> the 0.1 to 0.05 range. If you have moderate caching, using 1 and 2
> can be good.
>
> If you're still not getting reasonable plans, please post again with
> more information about your hardware along with the query and its
> EXPLAIN ANALYZE output.
>
> -Kevin
>
I understand the need to tune PostgreSQL properly for my use case.
What I am curious about is, for the data set I have, under what
circumstances (hardware/workload/cache status/etc) would a sequential
scan really be faster than an index scan for that particular query?
To simulate a scenario when nothing is cached, I stopped PostgreSQL,
dropped all system cache (sync; echo 3 > /proc/sys/vm/drop_caches),
restarted PostgreSQL, and ran the query. A sequential scan run took
13.70 seconds, while an index scan run took 0.34 seconds, which is
still 40 times faster.
Also, I tried increasing effective_cache_size from 512MB to 3GB (the
database size is 2+GB), and it still favor sequential scan. The
estimated costs did not change at all.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-27 23:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-28 00:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
@ 2011-04-28 11:32 ` Samuel Gendler <sgendler@ideasculptor.com>
1 sibling, 0 replies; 43+ messages in thread
From: Samuel Gendler @ 2011-04-28 11:32 UTC (permalink / raw)
To: Sok Ann Yap <sokann@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
On Wed, Apr 27, 2011 at 5:19 PM, Sok Ann Yap <sokann@gmail.com> wrote:
> On Thu, Apr 28, 2011 at 7:23 AM, Kevin Grittner
>
>
> I understand the need to tune PostgreSQL properly for my use case.
> What I am curious about is, for the data set I have, under what
> circumstances (hardware/workload/cache status/etc) would a sequential
> scan really be faster than an index scan for that particular query?
>
Possibly none on your hardware - if the index is likely to be in memory
along with the actual table rows. In which case, the cost for index scan
(random page cost) should be made much closer to the cost for sequential
access. It looks like the planner must use the same strategy on each
iteration of the loop - it can't do index scan for some values and
sequential scan for others, so it must be computing the cost as
sequential_cost * (number of entries(44)) versus random_cost * (number of
entries). If random page cost is unreasonably high, it's not hard to see
how it could wind up looking more expensive to the planner, causing it to
choose the sequential scan for each loop iteration. If it were able to
change strategy on each iteration, it would be able to accurately assess
cost for each iteration and choose the correct strategy for that value. As
soon as you set the costs closer to actual cost for your system, postgres
does make the correct choice. If there weren't enough memory that postgres
could be 'sure' that the index would remain in cache at least for the
duration of all 44 iterations due to high workload, it is easy to see how
the index scan might become significantly more expensive than the sequential
scan, since the index scan must also load the referenced page from the table
- postgres cannot get values directly from the index.
> To simulate a scenario when nothing is cached, I stopped PostgreSQL,
> dropped all system cache (sync; echo 3 > /proc/sys/vm/drop_caches),
> restarted PostgreSQL, and ran the query. A sequential scan run took
> 13.70 seconds, while an index scan run took 0.34 seconds, which is
> still 40 times faster.
>
> Also, I tried increasing effective_cache_size from 512MB to 3GB (the
> database size is 2+GB), and it still favor sequential scan. The
> estimated costs did not change at all.
>
Greg Smith had this to say in a another thread on this same subject:
effective_cache_size probably doesn't do as much as you suspect. It is used
for one of the computations for whether an index is small enough that it can
likely be read into memory efficiently. It has no impact on caching
decisions outside of that.
This is why the cost for random page access must be fairly accurate.Even if
the index is in memory, *it still needs to access the page of data in the
table referenced by the index*, which is why the cost of random access must
be accurate. That cost is a factor of both the performance of your storage
infrastructure and the cache hit rate and can't really be computed by the
database on the fly. You seem to be looking at the data which exposes the
fact that random page access is fast and wondering why postgres isn't doing
the right thing when postgres isn't doing the right thing precisely because
it doesn't know that random page access is fast. Since you don't have
particularly fast storage infrastructure, this is likely a function of cache
hit rate, so you must factor in eventual load on the db when setting this
value. While it may be fast in a lightly loaded test environment, those
random page accesses will get much more expensive when competing with other
concurrent disk access.
There's another thread currently active on this list (it started on April
12) with subject "Performance" which contains this explanation of what is
going on and why you need to tune these parameters independently of
effective_cache_size:
When the planner decides what execution plan to use,
it computes a 'virtual cost' for different plans and then chooses the
cheapest one.
Decreasing 'random_page_cost' decreases the expected cost of plans
involving index scans, so that at a certain point it seems cheaper than
a plan using sequential scans etc.
You can see this when using EXPLAIN - do it with the original cost
values, then change the values (for that session only) and do the
EXPLAIN only. You'll see how the execution plan suddenly changes and
starts to use index scans.
The problem with random I/O is that it's usually much more expensive
than sequential I/O as the drives need to seek etc. The only case when
random I/O is just as cheap as sequential I/O is when all the data is
cached in memory, because within RAM there's no difference between
random and sequential access (right, that's why it's called Random
Access Memory).
So in the previous post setting both random_page_cost and seq_page_cost
to the same value makes sense, because when the whole database fits into
the memory, there's no difference and index scans are favorable.
In this case (the database is much bigger than the available RAM) this
no longer holds - index scans hit the drives, resulting in a lot of
seeks etc. So it's a serious performance killer ...
Note: I'm not a postgres developer, so I don't often contribute to these
threads for fear of communicating misinformation. I'm sure someone will
speak up if I got it wrong, but I happened to read that other thread this
afternoon and I wasn't sure anyone else would bring it up, so I chimed in.
Take my input with a grain of salt until confirmed by someone with more
knowledge of postgres internals than I possess.
--sam
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: reducing random_page_cost from 4 to 2 to force index scan
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-27 23:23 ` Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-28 00:19 ` Re: reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
@ 2011-04-28 15:56 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 43+ messages in thread
From: Jeff Janes @ 2011-04-28 15:56 UTC (permalink / raw)
To: Sok Ann Yap <sokann@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
On Wed, Apr 27, 2011 at 5:19 PM, Sok Ann Yap <sokann@gmail.com> wrote:
>
> I understand the need to tune PostgreSQL properly for my use case.
> What I am curious about is, for the data set I have, under what
> circumstances (hardware/workload/cache status/etc) would a sequential
> scan really be faster than an index scan for that particular query?
The sequential scan on contacts can be terminated as soon as the first
matching row is found. If each block of the contacts table contains
one example of each salutation, then the inner sequential scan will
always be very short, and faster than an index scan.
I can engineer this to be the case by populating the table like this:
insert into contacts select (generate_series%44+1)::int from
generate_series (1,1000000);
Here I get the seq scan being 2.6ms while the index scan is 5.6ms.
Predicting how far the inner scan needs to go would be quite
difficult, and I don't know how the system will do it.
However, when I create and populate simple tables based on your
description, I get the index scan being the lower estimated cost. So
the tables I built are not sufficient to study the matter in detail.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 43+ messages in thread
end of thread, other threads:[~2011-05-24 09:12 UTC | newest]
Thread overview: 43+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-26 09:49 reducing random_page_cost from 4 to 2 to force index scan Sok Ann Yap <sokann@gmail.com>
2011-04-26 21:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-26 23:23 ` Sok Ann Yap <sokann@gmail.com>
2011-04-27 01:04 ` Merlin Moncure <mmoncure@gmail.com>
2011-04-27 07:22 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-27 07:23 ` Claudio Freire <klaussfreire@gmail.com>
2011-05-13 17:44 ` Robert Haas <robertmhaas@gmail.com>
2011-05-13 17:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-05-13 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-13 20:04 ` Robert Haas <robertmhaas@gmail.com>
2011-05-13 20:13 ` Josh Berkus <josh@agliodbs.com>
2011-05-13 20:51 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-14 02:20 ` Robert Haas <robertmhaas@gmail.com>
2011-05-15 18:08 ` Josh Berkus <josh@agliodbs.com>
2011-05-15 20:32 ` Robert Haas <robertmhaas@gmail.com>
2011-05-15 03:49 ` Stuart Bishop <stuart@stuartbishop.net>
2011-05-15 18:20 ` Josh Berkus <josh@agliodbs.com>
2011-05-15 21:45 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 01:05 ` Craig Ringer <craig@postnewspapers.com.au>
2011-05-16 01:18 ` Greg Smith <greg@2ndquadrant.com>
2011-05-16 04:41 ` Jesper Krogh <jesper@krogh.cc>
2011-05-16 04:49 ` Jesper Krogh <jesper@krogh.cc>
2011-05-16 14:34 ` Robert Haas <robertmhaas@gmail.com>
2011-05-16 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-17 19:19 ` Jim Nasby <jim@nasby.net>
2011-05-19 03:00 ` Greg Smith <greg@2ndquadrant.com>
2011-05-19 14:53 ` Robert Haas <robertmhaas@gmail.com>
2011-05-19 18:39 ` Jim Nasby <jim@nasby.net>
2011-05-19 20:07 ` Robert Haas <robertmhaas@gmail.com>
2011-05-23 19:08 ` Josh Berkus <josh@agliodbs.com>
2011-05-24 02:44 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 09:12 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-05-19 22:27 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-05-16 16:45 ` Jeff Janes <jeff.janes@gmail.com>
2011-05-16 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-16 19:10 ` Nathan Boley <npboley@gmail.com>
2011-05-16 19:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-27 12:40 Re: reducing random_page_cost from 4 to 2 to force index scan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-27 22:34 ` Sok Ann Yap <sokann@gmail.com>
2011-04-27 23:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-28 00:19 ` Sok Ann Yap <sokann@gmail.com>
2011-04-28 11:32 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-04-28 15:56 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox