[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: [PERFORM] NestedLoops over BitmapScan question
From: Виктор Егоров <vyegorov () gmail ! com>
Date: 2012-09-28 23:11:58
Message-ID: CAGnEbohKVrr7_YOU7SatewVazhWezZFM7E8bBv4NXuJhTq6gig () mail ! gmail ! com
[Download RAW message or body]
Greetings.
I have a small monitoring query on the following tables:
select relname,relpages,reltuples::numeric(12) from pg_class where relname
in ('meta_version','account') order by 1;
relname | relpages | reltuples
--------------+----------+-----------
account | 3235 | 197723
meta_version | 710068 | 32561200
(2 rows)
The logical "body" of the query is:
select count(*) from meta_version where account_id in (select account_id
from account where customer_id = 8608064);
I know that due to the data distribution (above customer's accounts are
used in 45% of the meta_version table) I
cannot expect fast results. But I have another question.
With default default_statistics_target I get the following plan:
http://explain.depesz.com/s/jri
In order to get better estimates, I've increased statistics targets to 200
for account.customer_id and meta_version.account_id.
Now I have the following plan:
http://explain.depesz.com/s/YZJ
Second query takes twice more time.
My questions are:
- why with better statistics planner chooses to do a SeqScan in favor of
BitmapIndexScan inside the NestedLoops?
- is it possible to adjust this decision by changing other GUCs, perhaps
costs?
- would it be correct to adjust seq_page_cost and random_page_cost based on
the IOPS of the underlying disks?
any other metrics should be considered?
I'm running on a:
name |
current_setting
----------------------------+---------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
archive_command | test ! -f $PG_WAL/%f && cp %p $PG_WAL/%f
archive_mode | on
bgwriter_delay | 50ms
bgwriter_lru_maxpages | 200
checkpoint_segments | 25
checkpoint_timeout | 30min
client_encoding | UTF8
effective_cache_size | 8GB
hot_standby | on
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_checkpoints | on
log_connections | on
log_destination | csvlog
log_directory | ../../log/CLUSTER
log_disconnections | on
log_file_mode | 0640
log_filename | pg-%Y%m%d_%H%M%S.log
log_line_prefix | %u:%d:%a:%h:%c:%x:%t>
log_lock_waits | on
log_min_duration_statement | 300ms
log_rotation_age | 1d
log_rotation_size | 0
log_temp_files | 20MB
logging_collector | on
maintenance_work_mem | 512MB
max_connections | 200
max_prepared_transactions | 0
max_stack_depth | 2MB
max_wal_senders | 2
port | 9120
server_encoding | UTF8
shared_buffers | 5GB
silent_mode | on
ssl | on
ssl_renegotiation_limit | 0
tcp_keepalives_idle | 0
temp_buffers | 256MB
TimeZone | US/Eastern
wal_buffers | 512kB
wal_keep_segments | 0
wal_level | hot_standby
wal_sender_delay | 1s
work_mem | 32MB
Regards.
--
Victor Y. Yegorov
[Attachment #3 (text/html)]
Greetings.<div><br></div><div>I have a small monitoring query on the following \
tables:</div><div><div><font size="1" face="courier new, monospace" \
color="#330099">select relname,relpages,reltuples::numeric(12) from pg_class where \
relname in ('meta_version','account') order by 1;</font></div> \
<div><font size="1" face="courier new, monospace" color="#330099"> relname | \
relpages | reltuples </font></div><div><font size="1" face="courier new, monospace" \
color="#330099">--------------+----------+-----------</font></div> <div><font \
size="1" face="courier new, monospace" color="#330099"> account | 3235 \
| 197723</font></div><div><font size="1" face="courier new, monospace" \
color="#330099"> meta_version | 710068 | 32561200</font></div> <div><font \
size="1" face="courier new, monospace" color="#330099">(2 \
rows)</font></div></div><div><br></div><div>The logical "body" of the query \
is:</div><div><font size="1" face="courier new, monospace" color="#330099">select \
count(*) from meta_version where account_id in (select account_id from account where \
customer_id = 8608064);</font></div> <div><br clear="all"><div>I know that due to the \
data distribution (above customer's accounts are used in 45% of the <font \
size="1" face="courier new, monospace" color="#330099">meta_version</font> table) \
I</div><div> cannot expect fast results. But I have another \
question.</div><div><br></div><div>With default <font size="1" face="courier new, \
monospace" color="#330099">default_statistics_target</font> I get the following \
plan:</div><div> <a href="http://explain.depesz.com/s/jri">http://explain.depesz.com/s/jri</a></div><div><br></div><div>In \
order to get better estimates, I've increased statistics targets to 200 for <font \
color="#330099" size="1" face="courier new, monospace">account.customer_id</font> and \
<font color="#330099" size="1" face="courier new, \
monospace">meta_version.account_id</font>.</div> <div>Now I have the following \
plan:</div><div><a href="http://explain.depesz.com/s/YZJ">http://explain.depesz.com/s/YZJ</a></div><div><br></div><div>Second \
query takes twice more time.</div><div>My questions are:</div><div>
- why with better statistics planner chooses to do a SeqScan in favor of \
BitmapIndexScan inside the NestedLoops?</div><div>- is it possible to adjust this \
decision by changing other GUCs, perhaps costs?</div><div>- would it be correct to \
adjust <font color="#330099" size="1" face="courier new, \
monospace">seq_page_cost</font> and <font color="#330099" size="1" face="courier \
new, monospace">random_page_cost</font> based on the IOPS of the underlying \
disks?</div> <div> any other metrics should be \
considered?</div><div><br></div><div>I'm running on a:</div><div><div><font \
color="#330099" size="1" face="courier new, monospace"> name \
| \
current_setting \
</font></div> <div><font color="#330099" size="1" face="courier new, \
monospace">----------------------------+---------------------------------------------- \
-----------------------------------------------------------------</font></div><div> \
<font color="#330099" size="1" face="courier new, monospace"> version \
| PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 \
(Red Hat 4.1.2-52), 64-bit</font></div><div><font color="#330099" size="1" \
face="courier new, monospace"> archive_command | test ! -f \
$PG_WAL/%f && cp %p $PG_WAL/%f</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> archive_mode | \
on</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
bgwriter_delay | 50ms</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> bgwriter_lru_maxpages | \
200</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
checkpoint_segments | 25</font></div> <div><font color="#330099" size="1" \
face="courier new, monospace"> checkpoint_timeout | \
30min</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
client_encoding | UTF8</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> effective_cache_size | \
8GB</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
hot_standby | on</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> lc_collate | \
en_US.UTF-8</font></div><div><font color="#330099" size="1" face="courier new, \
monospace"> lc_ctype | en_US.UTF-8</font></div> \
<div><font color="#330099" size="1" face="courier new, monospace"> listen_addresses \
| *</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
log_checkpoints | on</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> log_connections | \
on</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
log_destination | csvlog</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> log_directory | \
../../log/CLUSTER</font></div><div><font color="#330099" size="1" face="courier new, \
monospace"> log_disconnections | on</font></div> <div><font \
color="#330099" size="1" face="courier new, monospace"> log_file_mode \
| 0640</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
log_filename | pg-%Y%m%d_%H%M%S.log</font></div> <div><font \
color="#330099" size="1" face="courier new, monospace"> log_line_prefix \
| %u:%d:%a:%h:%c:%x:%t> </font></div><div><font color="#330099" size="1" \
face="courier new, monospace"> log_lock_waits | on</font></div> \
<div><font color="#330099" size="1" face="courier new, monospace"> \
log_min_duration_statement | 300ms</font></div><div><font color="#330099" size="1" \
face="courier new, monospace"> log_rotation_age | 1d</font></div> \
<div><font color="#330099" size="1" face="courier new, monospace"> log_rotation_size \
| 0</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
log_temp_files | 20MB</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> logging_collector | \
on</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
maintenance_work_mem | 512MB</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> max_connections | \
200</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
max_prepared_transactions | 0</font></div> <div><font color="#330099" size="1" \
face="courier new, monospace"> max_stack_depth | \
2MB</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
max_wal_senders | 2</font></div> <div><font color="#330099" size="1" \
face="courier new, monospace"> port | \
9120</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
server_encoding | UTF8</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> shared_buffers | \
5GB</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
silent_mode | on</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> ssl | \
on</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
ssl_renegotiation_limit | 0</font></div> <div><font color="#330099" size="1" \
face="courier new, monospace"> tcp_keepalives_idle | \
0</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
temp_buffers | 256MB</font></div> <div><font color="#330099" \
size="1" face="courier new, monospace"> TimeZone | \
US/Eastern</font></div><div><font color="#330099" size="1" face="courier new, \
monospace"> wal_buffers | 512kB</font></div> <div><font \
color="#330099" size="1" face="courier new, monospace"> wal_keep_segments \
| 0</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
wal_level | hot_standby</font></div> <div><font \
color="#330099" size="1" face="courier new, monospace"> wal_sender_delay \
| 1s</font></div><div><font color="#330099" size="1" face="courier new, monospace"> \
work_mem | 32MB</font></div> \
</div><div><br></div><div>Regards.</div><div><br></div>-- <br>Victor Y. Yegorov<br> \
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic