Another index related question.... - Mailing list pgsql-performance

Hey all, I have two tables that look like this:


CREATE TABLE details
(
   cust_code character varying(6) NOT NULL,
   cust_po character varying(20) NOT NULL,
   date_ordd date NOT NULL,
   item_nbr integer NOT NULL,
   orig_qty_ordd integer,
   CONSTRAINT details_pkey PRIMARY KEY (cust_code, cust_po, date_ordd,
item_nbr)
);
CREATE INDEX idx_details ON details USING btree (cust_code, cust_po,
date_ordd);


CREATE TABLE status
(
   id serial NOT NULL,
   cust_code character varying(6) NOT NULL,
   cust_po character varying(20) NOT NULL,
   date_ordd date NOT NULL,
   item_nbr integer,
   ext_nbr integer,
....
....
...
   CONSTRAINT status_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_status_idx2 ON status USING btree (cust_code,
cust_po, date_ordd, item_nbr);


Both tables are analyzed full and Table details contains around
390.000 records and status contains around 580.000 records.


Doing the following SQL:
explain analyze
SELECT
a
.cust_code
,a
.cust_po
,a.date_ordd,a.item_nbr,b.Lines_part_ordd,a.orig_qty_ordd,b.Ship_via
FROM details a
JOIN status b ON (a.cust_code = b.cust_code AND a.cust_po = b.cust_po
AND a.date_ordd = b.date_ordd AND  a.item_nbr = b.item_nbr )

Created this execution plan

Merge Join  (cost=0.76..71872.13 rows=331 width=41) (actual
time=0.393..225404.902 rows=579742 loops=1)
   Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd = b.date_ordd))
   Join Filter: (a.item_nbr = b.item_nbr)
   ->  Index Scan using idx_details on details a  (cost=0.00..23847.74
rows=389147 width=28) (actual time=0.244..927.752 rows=389147 loops=1)
   ->  Index Scan using idx_status_idx2 on status b
(cost=0.00..40249.31 rows=579933 width=37) (actual
time=0.142..84250.016 rows=176701093 loops=1)
Total runtime: 225541.232 ms

Question to myself is why does it want to do a Join Filter on item_nbr?

When drop the index idx_details I get this execution plan:

Merge Join  (cost=0.81..74650.36 rows=331 width=41) (actual
time=0.106..2159.315 rows=579742 loops=1)
   Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd =
b.date_ordd) AND (a.item_nbr = b.item_nbr))
   ->  Index Scan using details_pkey on details a
(cost=0.00..24707.75 rows=389147 width=28) (actual time=0.030..562.234
rows=389147 loops=1)
   ->  Index Scan using idx_status_idx2 on status b
(cost=0.00..40249.31 rows=579933 width=37) (actual time=0.069..289.359
rows=579933 loops=1)
Total runtime: 2226.793 ms

Notice the difference in speed, the second one is about 100 times
faster.

As per Tom's advice I tried to set random_page_cost to 2, but that
doesn't change my execution plan, also not with 1...

Now  I add the index idx_details back again.......

And when I set my cpu_tuple_cost to 0.25 I get the 'correct' execution
plan as shown above.

set cpu_tuple_cost=0.25;
explain analyze
SELECT
a
.cust_code
,a
.cust_po
,a.date_ordd,a.item_nbr,b.Lines_part_ordd,a.orig_qty_ordd,b.Ship_via
FROM acc_sc.details a
JOIN acc_sc.status b ON (a.cust_code = b.cust_code AND a.cust_po =
b.cust_po AND a.date_ordd = b.date_ordd AND  a.item_nbr = b.item_nbr )

This execution plan is the same as the one above when idx_details was
removed

Merge Join  (cost=3.45..307306.36 rows=331 width=41) (actual
time=0.038..2246.726 rows=579742 loops=1)
   Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd =
b.date_ordd) AND (a.item_nbr = b.item_nbr))
   ->  Index Scan using details_pkey on details a
(cost=0.00..118103.03 rows=389147 width=28) (actual
time=0.020..589.779 rows=389147 loops=1)
   ->  Index Scan using idx_status_idx2 on status b
(cost=0.00..179433.23 rows=579933 width=37) (actual
time=0.011..305.963 rows=579933 loops=1)
Total runtime: 2318.647 ms



What I am trying to understand is why PostgreSQL want's to use
idx_details over it's primary_key?
I am sure that 'simply' setting cpu_tuple_cost to 0.25 from 0.01 is
not a good idea......?



Ries van Twisk


SHOW ALL;
add_missing_from;off;Automatically adds missing table references to
FROM clauses.
allow_system_table_mods;off;Allows modifications of the structure of
system tables.
archive_command;(disabled);Sets the shell command that will be called
to archive a WAL file.
archive_mode;off;Allows archiving of WAL files using archive_command.
archive_timeout;0;Forces a switch to the next xlog file if a new file
has not been started within N seconds.
array_nulls;on;Enable input of NULL elements in arrays.
authentication_timeout;1min;Sets the maximum allowed time to complete
client authentication.
autovacuum;on;Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor;0.1;Number of tuple inserts, updates
or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold;50;Minimum number of tuple inserts,
updates or deletes prior to analyze.
autovacuum_freeze_max_age;200000000;Age at which to autovacuum a table
to prevent transaction ID wraparound.
autovacuum_max_workers;3;Sets the maximum number of simultaneously
running autovacuum worker processes.
autovacuum_naptime;1min;Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay;20ms;Vacuum cost delay in milliseconds,
for autovacuum.
autovacuum_vacuum_cost_limit;-1;Vacuum cost amount available before
napping, for autovacuum.
autovacuum_vacuum_scale_factor;0.2;Number of tuple updates or deletes
prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold;50;Minimum number of tuple updates or
deletes prior to vacuum.
backslash_quote;safe_encoding;Sets whether "\'" is allowed in string
literals.
bgwriter_delay;200ms;Background writer sleep time between rounds.
bgwriter_lru_maxpages;100;Background writer maximum number of LRU
pages to flush per round.
bgwriter_lru_multiplier;2;Background writer multiplier on average
buffers to scan per round.
block_size;8192;Shows the size of a disk block.
bonjour_name;;Sets the Bonjour broadcast service name.
check_function_bodies;on;Check function bodies during CREATE FUNCTION.
checkpoint_completion_target;0.5;Time spent flushing dirty buffers
during checkpoint, as fraction of checkpoint interval.
checkpoint_segments;3;Sets the maximum distance in log segments
between automatic WAL checkpoints.
checkpoint_timeout;5min;Sets the maximum time between automatic WAL
checkpoints.
checkpoint_warning;30s;Enables warnings if checkpoint segments are
filled more frequently than this.
client_encoding;UNICODE;Sets the client's character set encoding.
client_min_messages;notice;Sets the message levels that are sent to
the client.
commit_delay;0;Sets the delay in microseconds between transaction
commit and flushing WAL to disk.
commit_siblings;5;Sets the minimum concurrent open transactions before
performing commit_delay.
config_file;/usr/local/pgsql/data/postgresql.conf;Sets the server's
main configuration file.
constraint_exclusion;off;Enables the planner to use constraints to
optimize queries.
cpu_index_tuple_cost;0.005;Sets the planner's estimate of the cost of
processing each index entry during an index scan.
cpu_operator_cost;0.0025;Sets the planner's estimate of the cost of
processing each operator or function call.
cpu_tuple_cost;0.01;Sets the planner's estimate of the cost of
processing each tuple (row).
custom_variable_classes;;Sets the list of known custom variable classes.
data_directory;/usr/local/pgsql/data;Sets the server's data directory.
DateStyle;ISO, MDY;Sets the display format for date and time values.
db_user_namespace;off;Enables per-database user names.
deadlock_timeout;1s;Sets the time to wait on a lock before checking
for deadlock.
debug_assertions;off;Turns on various assertion checks.
debug_pretty_print;off;Indents parse and plan tree displays.
debug_print_parse;off;Prints the parse tree to the server log.
debug_print_plan;off;Prints the execution plan to server log.
debug_print_rewritten;off;Prints the parse tree after rewriting to
server log.
default_statistics_target;10;Sets the default statistics target.
default_tablespace;;Sets the default tablespace to create tables and
indexes in.
default_text_search_config;pg_catalog.simple;Sets default text search
configuration.
default_transaction_isolation;read committed;Sets the transaction
isolation level of each new transaction.
default_transaction_read_only;off;Sets the default read-only status of
new transactions.
default_with_oids;off;Create new tables with OIDs by default.
dynamic_library_path;$libdir;Sets the path for dynamically loadable
modules.
effective_cache_size;1GB;Sets the planner's assumption about the size
of the disk cache.
enable_bitmapscan;on;Enables the planner's use of bitmap-scan plans.
enable_hashagg;on;Enables the planner's use of hashed aggregation plans.
enable_hashjoin;on;Enables the planner's use of hash join plans.
enable_indexscan;on;Enables the planner's use of index-scan plans.
enable_mergejoin;on;Enables the planner's use of merge join plans.
enable_nestloop;on;Enables the planner's use of nested-loop join plans.
enable_seqscan;on;Enables the planner's use of sequential-scan plans.
enable_sort;on;Enables the planner's use of explicit sort steps.
enable_tidscan;on;Enables the planner's use of TID scan plans.
escape_string_warning;on;Warn about backslash escapes in ordinary
string literals.
explain_pretty_print;on;Uses the indented output format for EXPLAIN
VERBOSE.
external_pid_file;;Writes the postmaster PID to the specified file.
extra_float_digits;0;Sets the number of digits displayed for floating-
point values.
from_collapse_limit;8;Sets the FROM-list size beyond which subqueries
are not collapsed.
fsync;on;Forces synchronization of updates to disk.
full_page_writes;on;Writes full pages to WAL when first modified after
a checkpoint.
geqo;on;Enables genetic query optimization.
geqo_effort;5;GEQO: effort is used to set the default for other GEQO
parameters.
geqo_generations;0;GEQO: number of iterations of the algorithm.
geqo_pool_size;0;GEQO: number of individuals in the population.
geqo_selection_bias;2;GEQO: selective pressure within the population.
geqo_threshold;12;Sets the threshold of FROM items beyond which GEQO
is used.
gin_fuzzy_search_limit;0;Sets the maximum allowed result for exact
search by GIN.
hba_file;/usr/local/pgsql/data/pg_hba.conf;Sets the server's "hba"
configuration file.
ident_file;/usr/local/pgsql/data/pg_ident.conf;Sets the server's
"ident" configuration file.
ignore_system_indexes;off;Disables reading from system indexes.
integer_datetimes;off;Datetimes are integer based.
join_collapse_limit;8;Sets the FROM-list size beyond which JOIN
constructs are not flattened.
krb_caseins_users;off;Sets whether Kerberos and GSSAPI user names
should be treated as case-insensitive.
krb_realm;;Sets realm to match Kerberos and GSSAPI users against.
krb_server_hostname;;Sets the hostname of the Kerberos server.
krb_server_keyfile;;Sets the location of the Kerberos server key file.
krb_srvname;postgres;Sets the name of the Kerberos service.
lc_collate;C;Shows the collation order locale.
lc_ctype;C;Shows the character classification and case conversion
locale.
lc_messages;;Sets the language in which messages are displayed.
lc_monetary;C;Sets the locale for formatting monetary amounts.
lc_numeric;C;Sets the locale for formatting numbers.
lc_time;C;Sets the locale for formatting date and time values.
listen_addresses;*;Sets the host name or IP address(es) to listen to.
local_preload_libraries;;Lists shared libraries to preload into each
backend.
log_autovacuum_min_duration;-1;Sets the minimum execution time above
which autovacuum actions will be logged.
log_checkpoints;off;Logs each checkpoint.
log_connections;off;Logs each successful connection.
log_destination;stderr;Sets the destination for server log output.
log_directory;pg_log;Sets the destination directory for log files.
log_disconnections;off;Logs end of a session, including duration.
log_duration;off;Logs the duration of each completed SQL statement.
log_error_verbosity;default;Sets the verbosity of logged messages.
log_executor_stats;off;Writes executor performance statistics to the
server log.
log_filename;postgresql-%Y-%m-%d_%H%M%S.log;Sets the file name pattern
for log files.
log_hostname;off;Logs the host name in the connection logs.
log_line_prefix;;Controls information prefixed to each log line.
log_lock_waits;off;Logs long lock waits.
log_min_duration_statement;-1;Sets the minimum execution time above
which statements will be logged.
log_min_error_statement;error;Causes all statements generating error
at or above this level to be logged.
log_min_messages;notice;Sets the message levels that are logged.
log_parser_stats;off;Writes parser performance statistics to the
server log.
log_planner_stats;off;Writes planner performance statistics to the
server log.
log_rotation_age;1d;Automatic log file rotation will occur after N
minutes.
log_rotation_size;10MB;Automatic log file rotation will occur after N
kilobytes.
log_statement;none;Sets the type of statements logged.
log_statement_stats;off;Writes cumulative performance statistics to
the server log.
log_temp_files;-1;Log the use of temporary files larger than this
number of kilobytes.
log_timezone;America/Guayaquil;Sets the time zone to use in log
messages.
log_truncate_on_rotation;off;Truncate existing log files of same name
during log rotation.
logging_collector;off;Start a subprocess to capture stderr output and/
or csvlogs into log files.
maintenance_work_mem;256MB;Sets the maximum memory to be used for
maintenance operations.
max_connections;100;Sets the maximum number of concurrent connections.
max_files_per_process;1000;Sets the maximum number of simultaneously
open files for each server process.
max_fsm_pages;524288;Sets the maximum number of disk pages for which
free space is tracked.
max_fsm_relations;1000;Sets the maximum number of tables and indexes
for which free space is tracked.
max_function_args;100;Shows the maximum number of function arguments.
max_identifier_length;63;Shows the maximum identifier length.
max_index_keys;32;Shows the maximum number of index keys.
max_locks_per_transaction;64;Sets the maximum number of locks per
transaction.
max_prepared_transactions;5;Sets the maximum number of simultaneously
prepared transactions.
max_stack_depth;2MB;Sets the maximum stack depth, in kilobytes.
password_encryption;on;Encrypt passwords.
port;5432;Sets the TCP port the server listens on.
post_auth_delay;0;Waits N seconds on connection startup after
authentication.
pre_auth_delay;0;Waits N seconds on connection startup before
authentication.
random_page_cost;4;Sets the planner's estimate of the cost of a
nonsequentially fetched disk page.
regex_flavor;advanced;Sets the regular expression "flavor".
search_path;tmp, public;Sets the schema search order for names that
are not schema-qualified.
seq_page_cost;1;Sets the planner's estimate of the cost of a
sequentially fetched disk page.
server_encoding;UTF8;Sets the server (database) character set encoding.
server_version;8.3.1;Shows the server version.
server_version_num;80301;Shows the server version as an integer.
session_replication_role;origin;Sets the session's behavior for
triggers and rewrite rules.
shared_buffers;192MB;Sets the number of shared memory buffers used by
the server.
shared_preload_libraries;;Lists shared libraries to preload into server.
silent_mode;off;Runs the server silently.
sql_inheritance;on;Causes subtables to be included by default in
various commands.
ssl;off;Enables SSL connections.
standard_conforming_strings;off;Causes '...' strings to treat
backslashes literally.
statement_timeout;0;Sets the maximum allowed duration of any statement.
superuser_reserved_connections;3;Sets the number of connection slots
reserved for superusers.
synchronize_seqscans;on;Enable synchronized sequential scans.
synchronous_commit;on;Sets immediate fsync at commit.
syslog_facility;LOCAL0;Sets the syslog "facility" to be used when
syslog enabled.
syslog_ident;postgres;Sets the program name used to identify
PostgreSQL messages in syslog.
tcp_keepalives_count;0;Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle;0;Time between issuing TCP keepalives.
tcp_keepalives_interval;0;Time between TCP keepalive retransmits.
temp_buffers;1024;Sets the maximum number of temporary buffers used by
each session.
temp_tablespaces;;Sets the tablespace(s) to use for temporary tables
and sort files.
TimeZone;America/Guayaquil;Sets the time zone for displaying and
interpreting time stamps.
timezone_abbreviations;Default;Selects a file of time zone
abbreviations.
trace_notify;off;Generates debugging output for LISTEN and NOTIFY.
trace_sort;off;Emit information about resource usage in sorting.
track_activities;on;Collects information about executing commands.
track_counts;on;Collects statistics on database activity.
transaction_isolation;read committed;Sets the current transaction's
isolation level.
transaction_read_only;off;Sets the current transaction's read-only
status.
transform_null_equals;off;Treats "expr=NULL" as "expr IS NULL".
unix_socket_directory;;Sets the directory where the Unix-domain socket
will be created.
unix_socket_group;;Sets the owning group of the Unix-domain socket.
unix_socket_permissions;511;Sets the access permissions of the Unix-
domain socket.
update_process_title;on;Updates the process title to show the active
SQL command.
vacuum_cost_delay;0;Vacuum cost delay in milliseconds.
vacuum_cost_limit;200;Vacuum cost amount available before napping.
vacuum_cost_page_dirty;20;Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit;1;Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss;10;Vacuum cost for a page not found in the
buffer cache.
vacuum_freeze_min_age;100000000;Minimum age at which VACUUM should
freeze a table row.
wal_buffers;8MB;Sets the number of disk-page buffers in shared memory
for WAL.
wal_sync_method;fsync;Selects the method used for forcing WAL updates
to disk.
wal_writer_delay;200ms;WAL writer sleep time between WAL flushes.
work_mem;4MB;Sets the maximum memory to be used for query workspaces.
xmlbinary;base64;Sets how binary values are to be encoded in XML.
xmloption;content;Sets whether XML data in implicit parsing and
serialization operations is to be considered as documents or content
fragments.
zero_damaged_pages;off;Continues processing past damaged page headers.