-----Original Message-----
From: pgsql-performance-owner@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of
Charlie Savage
Sent: Tuesday, November 08, 2005 2:05 AM
To: pgsql-performance@xxxxxxxxxxxxxx
Subject: Sort performance on large tables
Hi everyone,
I have a question about the performance of sort.
Setup: Dell Dimension 3000, Suse 10, 1GB ram, PostgreSQL 8.1
RC 1 with PostGIS, 1 built-in 80 GB IDE drive, 1 SATA Seagate
400GB drive. The IDE drive has the OS and the WAL files, the
SATA drive the database.
From hdparm the max IO for the IDE drive is about 50Mb/s and
the SATA drive is about 65Mb/s. Thus a very low-end machine
- but it used just for development (i.e., it is not a
production machine) and the only thing it does is run a
PostgresSQL database.
I have a staging table called completechain that holds US
tiger data (i.e., streets and addresses for the US). The
table is approximately 18GB. Its big because there is a lot
of data, but also because the table is not normalized (it
comes that way).
I want to extract data out of the file, with the most
important values being stored in a column called tlid. The
tlid field is an integer, and the values are 98% unique.
There is a second column called ogc_fid which is unique (it
is a serial field). I need to extract out unique TLID's
(doesn't matter which duplicate I get rid of). To do this I
am running this query:
SELECT tlid, min(ogc_fid)
FROM completechain
GROUP BY tlid;
The results from explain analyze are:
"GroupAggregate (cost=10400373.80..11361807.88 rows=48071704
width=8) (actual time=7311682.715..8315746.835 rows=47599910 loops=1)"
" -> Sort (cost=10400373.80..10520553.06 rows=48071704
width=8) (actual time=7311682.682..7972304.777 rows=48199165 loops=1)"
" Sort Key: tlid"
" -> Seq Scan on completechain (cost=0.00..2228584.04
rows=48071704 width=8) (actual time=27.514..773245.046
rows=48199165 loops=1)"
"Total runtime: 8486057.185 ms"
Doing a similar query produces the same results:
SELECT DISTINCT ON (tlid), tlid, ogc_fid FROM completechain;
Note it takes over 10 times longer to do the sort than the
full sequential scan.
Should I expect results like this? I realize that the
computer is quite low-end and is very IO bound for this
query, but I'm still surprised that the sort operation takes so long.
Out of curiosity, I setup an Oracle database on the same
machine with the same data and ran the same query. Oracle
was over an order of magnitude faster. Looking at its query
plan, it avoided the sort by using "HASH GROUP BY." Does
such a construct exist in PostgreSQL (I see only hash joins)?
Also as an experiment I forced oracle to do a sort by running
this query:
SELECT tlid, min(ogc_fid)
FROM completechain
GROUP BY tlid
ORDER BY tlid;
Even with this, it was more than a magnitude faster than Postgresql.
Which makes me think I have somehow misconfigured postgresql
(see the relevant parts of postgresql.conf below).
Any idea/help appreciated.
Thanks,
Charlie
-------------------------------
#-------------------------------------------------------------
--------------
# RESOURCE USAGE (except WAL)
#-------------------------------------------------------------
--------------
shared_buffers = 40000 # 40000 buffers * 8192
bytes/buffer = 327,680,000 bytes
#shared_buffers = 1000 # min 16 or
max_connections*2, 8KB each
temp_buffers = 5000
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes
of shared memory # per transaction slot, plus lock space (see
max_locks_per_transaction).
work_mem = 16384 # in Kb
#work_mem = 1024 # min 64, size in KB
maintenance_work_mem = 262144 # in kb
#maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 60000
#max_fsm_pages = 20000 # min
max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds
between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max
written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers
scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max
written/round
#-------------------------------------------------------------
--------------
# WRITE AHEAD LOG
#-------------------------------------------------------------
--------------
# - Settings -
fsync = on # turns forced
synchronization on or off
#wal_sync_method = fsync # the default is the
first option
# supported by the
operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from
partial page writes
wal_buffers = 128
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in
microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 256 # 256 * 16Mb =
4,294,967,296 bytes
checkpoint_timeout = 1200 # 1200 seconds (20 minutes)
checkpoint_warning = 30 # in seconds, 0 is off
#checkpoint_segments = 3 # in logfile segments,
min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to
archive a logfile
# segment
#-------------------------------------------------------------
--------------
# QUERY TUNING
#-------------------------------------------------------------
--------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 80000 # 80000 * 8192 =
655,360,000 bytes
#effective_cache_size = 1000 # typically 8KB each
random_page_cost = 2.5 # units are one
sequential page fetch
#random_page_cost = 4 # units are one
sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based
on effort
#geqo_generations = 0 # selects default based
on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
default_statistics_target = 100 # range 1-1000
#default_statistics_target = 10 # range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing
of explicit
# JOINs
#-------------------------------------------------------------
--------------
#-------------------------------------------------------------
--------------
# RUNTIME STATISTICS
#-------------------------------------------------------------
--------------
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
# - Query/Index Statistics Collector -
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
#stats_start_collector = on
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
#stats_reset_on_server_start = off
#-------------------------------------------------------------
--------------
# AUTOVACUUM PARAMETERS
#-------------------------------------------------------------
--------------
autovacuum = true
autovacuum_naptime = 600
#autovacuum = false # enable autovacuum subprocess?
#autovacuum_naptime = 60 # time between
autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of
rel size before
# analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_
----------------------
CREATE TABLE tiger.completechain
(
ogc_fid int4 NOT NULL DEFAULT
nextval('completechain_ogc_fid_seq'::regclass),
module varchar(8) NOT NULL,
tlid int4 NOT NULL,
side1 int4,
source varchar(1) NOT NULL,
fedirp varchar(2),
fename varchar(30),
fetype varchar(4),
fedirs varchar(2),
cfcc varchar(3) NOT NULL,
fraddl varchar(11),
toaddl varchar(11),
fraddr varchar(11),
toaddr varchar(11),
friaddl varchar(1),
toiaddl varchar(1),
friaddr varchar(1),
toiaddr varchar(1),
zipl int4,
zipr int4,
aianhhfpl int4,
aianhhfpr int4,
aihhtlil varchar(1),
aihhtlir varchar(1),
census1 varchar(1),
census2 varchar(1),
statel int4,
stater int4,
countyl int4,
countyr int4,
cousubl int4,
cousubr int4,
submcdl int4,
submcdr int4,
placel int4,
placer int4,
tractl int4,
tractr int4,
blockl int4,
blockr int4,
wkb_geometry public.geometry NOT NULL,
CONSTRAINT enforce_dims_wkb_geometry CHECK
(ndims(wkb_geometry) = 2),
CONSTRAINT enforce_geotype_wkb_geometry CHECK
(geometrytype(wkb_geometry) = 'LINESTRING'::text OR
wkb_geometry IS NULL),
CONSTRAINT enforce_srid_wkb_geometry CHECK
(srid(wkb_geometry) = 4269)
)
WITHOUT OIDS;
ALTER TABLE tiger.completechain OWNER TO postgres;
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
