Thread: TRUNCATE veeeery slow compared to DELETE in 7.4

has anyone else noticed a huge difference in "DELETE TABLE <lol>"
vs. "TRUNCATE <lol>" starting w/postgres 7.4?
putting aside details (num rows, indexes....): ca. 300 tables
(already empty if desired...) ALL to be emptied (via batch file).
here's a small "time pgsql -f kill_all" output:

DELETE:
1) 0.03u 0.04s 0:02.46 2.8% (already empty)
2) 0.05u 0.06s 0:01.19 9.2% (already empty)

TRUNCATE:
1) 0.10u 0.06s 6:58.66 0.0% (already empty, compile runnig simult.)
2) 0.10u 0.02s 2:51.71 0.0% (already empty)

lovely, innit?

settings in 7.4 (wal, shm...) are as for 7.3.x unless dead or (in their
7.4 default version) even higher.

glimpsing at the quantify output (of the truncate version) it looks
as if this is "for (i = 0; i < all; i++)" whereas (from exec. time)
delete does "\rm -rf"

is this a pay-off for autocommit gone away?
a conspiracy?
...what am i saying...

we are using TRUNCATE btw, because someone once noted that this was
"good style", saying: "yes, i want to empty the whole thing", not:
"oops! forgot the where-clause, sorry for your table!"

well, enlight me, please!

P.S.: Grammarians dispute - and the case is still before the courts.
        - Horace, Epistles (Ars Poetica)

--
Hartmut "Hardy" Raschick / Dept. t2
ke Kommunikations-Elektronik GmbH
Wohlenberstr. 3, 30179 Hannover
Phone: ++49 (0)511 6747-564
Fax: ++49 (0)511 6747-340
e-Mail: hartmut.raschick@ke-elektronik.de
http://www.ke-elektronik.de

Hartmut,

> DELETE:
> 1) 0.03u 0.04s 0:02.46 2.8% (already empty)
> 2) 0.05u 0.06s 0:01.19 9.2% (already empty)
>
> TRUNCATE:
> 1) 0.10u 0.06s 6:58.66 0.0% (already empty, compile runnig simult.)
> 2) 0.10u 0.02s 2:51.71 0.0% (already empty)

How about some times for a full table?

Incidentally, I believe that TRUNCATE has always been slightly slower than
DROP TABLE.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> Incidentally, I believe that TRUNCATE has always been slightly slower than
> DROP TABLE.

Well, it would be: it has to delete the original files and then create
new ones.  I imagine the time to create new, empty indexes is the bulk
of the time Hartmut is measuring.  (Remember that an "empty" index has
at least one page in it, the metadata page, for all of our index types,
so there is some actual I/O involved to do this.)

It does not bother me that TRUNCATE takes nonzero time; it's intended
to be used in situations where DELETE would take huge amounts of time
(especially after you factor in the subsequent VACUUM activity).
The fact that DELETE takes near-zero time on a zero-length table is
not very relevant.

            regards, tom lane

for the clearer understanding: this is NOT about TRUNCATE being
slow "as such" vs. DELETE, but about a change in the order of
a (...) magnitude from 7.3.4 to 7.4...

here's some more info, plus test results w/a "full" db:

300 tables, 20000 pieces of modelled hw, so there's one table
w/20000 entries, each model has a special table (per type), too;
so, entries over all of them sum up to 20000; not all types are
present.
plus: some types (w/not many instances) have "very special" tables,
too, these sometimes w/lots of columns 500-1600...

alone on a sun fire-280 w/2 U-IIIi cpu's (well, only need one...):
all the time of the test, no vacuum anything was performed,
thus - by the book - making things worse... for the DELETE case.

7.4:
----
"full" database:
TRUNCATE: 0.03u 0.03s 1:21.40 0.0%
DELETE: 0.05u 0.01s 0:04.46 1.3%

empty database:
TRUNCATE:0.02u 0.05s 1:21.00 0.0%
DELETE: 0.04u 0.04s 0:01.32 6.0%

now for 7.3.4 database server (same machine, of cause):
--------------
"full" database:
TRUNCATE: 0.04u 0.04s 0:03.79 2.1%
DELETE: 0.03u 0.03s 0:06.51 0.9%

empty database:
TRUNCATE: 0.04u 0.05s 0:01.51 5.9%
DELETE: 0.01u 0.02s 0:01.00 3.0%

what can i say...
...please find the attached configs.

i reeeeally don't think this can be explained by table/index
complexity, it's the _same_ schema and contents for both cases,
they both were started w/createdb, they both were filled the same
way (by our server prog), there was no vacuum nowhere, test execution
order was the same in both cases.

P.S.: Mon pessimisme va jusqu'à suspecter la sincérité des pessimistes.
        - Jean Rostand (1894-1977), Journal d'un caractère, 1931

--
Hartmut "Hardy" Raschick / Dept. t2
ke Kommunikations-Elektronik GmbH
Wohlenberstr. 3, 30179 Hannover
Phone: ++49 (0)511 6747-564
Fax: ++49 (0)511 6747-340
e-Mail: hartmut.raschick@ke-elektronik.de
http://www.ke-elektronik.de#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".


#========================================================================


#
#    Connection Parameters
#
#tcpip_socket = false
tcpip_socket = true
#ssl = false

#max_connections = 32
max_connections = 128
#superuser_reserved_connections = 2

#port = 5432
port = 5433
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777    # octal

#virtual_host = ''

#krb_server_keyfile = ''


#
#    Shared Memory Size
#
#shared_buffers = 64        # min max_connections*2 or 16, 8KB each
shared_buffers = 256           # 2*max_connections, min 16, typically 8KB each
#max_fsm_relations = 1000    # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000        # min 1000, fsm is free space map, ~6 bytes
max_fsm_pages = 50000          # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64    # min 10
#wal_buffers = 8        # min 4, typically 8KB each
wal_buffers = 40              # min 4, typically 8KB each

#
#    Non-shared Memory Sizes
#
#sort_mem = 1024        # min 64, size in KB
sort_mem = 16384                # min 64, size in KB
#vacuum_mem = 8192        # min 1024, size in KB
vacuum_mem = 16384              # min 1024, size in KB


#
#    Write-ahead log (WAL)
#
#checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
checkpoint_segments = 20        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300    # range 30-3600, in seconds
checkpoint_timeout = 600        # range 30-3600, in seconds
#
#commit_delay = 0               # range 0-100000, in microseconds
commit_delay = 100000            # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000
commit_siblings = 100           # range 1-1000
#
#fsync = true
fsync = false
#wal_sync_method = fsync    # the default varies across platforms:
wal_sync_method = fdatasync    # the default varies across platforms:
#                # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0            # range 0-16


#
#    Optimizer Parameters
#
#enable_seqscan = true
enable_seqscan = false
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#effective_cache_size = 1000    # typically 8KB each
#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)

#default_statistics_target = 10    # range 1-1000

#
#    GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0    # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0        # default based on tables in statement,
                # range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1        # auto-compute seed


#
#    Message display
#
#server_min_messages = notice    # Values, in order of decreasing detail:
                #   debug5, debug4, debug3, debug2, debug1,
                #   info, notice, warning, error, log, fatal,
                #   panic
#client_min_messages = notice    # Values, in order of decreasing detail:
                #   debug5, debug4, debug3, debug2, debug1,
                #   log, info, notice, warning, error
#silent_mode = false

#log_connections = false
log_connections = true
#log_pid = false
#log_statement = false
#log_duration = false
#log_timestamp = false

#log_min_error_statement = error # Values in order of increasing severity:
                 #   debug5, debug4, debug3, debug2, debug1,
                 #   info, notice, warning, error, panic(off)

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#explain_pretty_print = true

# requires USE_ASSERT_CHECKING
#debug_assertions = true


#
#    Syslog
#
#syslog = 0            # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


#
#    Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_statement_stats = false

# requires BTREE_BUILD_STATS
#show_btree_build_stats = false


#
#    Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false


#
#    Lock Tracing
#
#trace_notify = false

# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0


#
#    Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
#datestyle = 'iso, us'
#timezone = unknown        # actually, defaults to TZ environment setting
#australian_timezones = false
#client_encoding = sql_ascii    # actually, defaults to database encoding
#authentication_timeout = 60    # 1-600, in seconds
#deadlock_timeout = 1000    # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000        # min 10
#max_files_per_process = 1000    # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0        # 0 is disabled, in milliseconds
#db_user_namespace = false





#
#    Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'C'
LC_MONETARY = 'en_US.ISO8859-15'
LC_NUMERIC = 'en_US.ISO8859-15'
LC_TIME = 'en_US.ISO8859-15'
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".


#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

#tcpip_socket = false
tcpip_socket = true
max_connections = 128
    # note: increasing max_connections costs about 500 bytes of shared
    # memory per connection slot, in addition to costs from shared_buffers
        # and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
port = 5474
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777    # octal
#virtual_host = ''        # what interface to listen on; defaults to any
#rendezvous_name = ''        # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 1024    # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024        # min 64, size in KB
sort_mem = 16384        # min 64, size in KB
#vacuum_mem = 8192        # min 1024, size in KB
vacuum_mem = 16384        # min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000        # min max_fsm_relations*16, 6 bytes each
max_fsm_pages = 50000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000    # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000    # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = true            # turns forced synchronization on or off
fsync = false            # turns forced synchronization on or off
#wal_sync_method = fsync    # the default varies across platforms:
wal_sync_method = fdatasync    # the default varies across platforms:
                # fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8        # min 4, 8KB each
wal_buffers = 40        # min 4, 8KB each

# - Checkpoints -

#checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
checkpoint_segments = 20        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300    # range 30-3600, in seconds
checkpoint_timeout = 600        # range 30-3600, in seconds
#checkpoint_warning = 30    # 0 is off, in seconds
#commit_delay = 0        # range 0-100000, in microseconds
commit_delay = 100000        # range 0-100000, in microseconds
#commit_siblings = 5        # range 1-1000
commit_siblings = 100        # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Enabling -

#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

#effective_cache_size = 1000    # typically 8KB each
#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 = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0        # default based on tables in statement,
                # range 128-1024
#geqo_selection_bias = 2.0    # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10    # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8    # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

#syslog = 0            # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice    # Values, in order of decreasing detail:
                #   debug5, debug4, debug3, debug2, debug1,
                #   log, info, notice, warning, error

#log_min_messages = notice    # Values, in order of decreasing detail:
                #   debug5, debug4, debug3, debug2, debug1,
                #   info, notice, warning, error, log, fatal,
                #   panic

#log_error_verbosity = default   # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
                 #   debug5, debug4, debug3, debug2, debug1,
                 #   info, notice, warning, error, panic(off)

#log_min_duration_statement = -1 # Log all statements whose
                 # execution time exceeds the value, in
                 # milliseconds.  Zero prints all queries.
                 # Minus-one disables.

#silent_mode = false         # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
log_connections = true
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false


#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false

# - Query/Index Statistics Collector -

#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'    # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0        # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown        # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0        # min -15, max 2
#client_encoding = sql_ascii    # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
lc_messages = 'C'        # locale for system error message strings
lc_monetary = 'en_US.ISO8859-15'        # locale for monetary formatting
lc_numeric = 'en_US.ISO8859-15'        # locale for number formatting
lc_time = 'en_US.ISO8859-15'            # locale for time formatting

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000        # min 10

#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1000    # in milliseconds
#max_locks_per_transaction = 64    # min 10, ~260*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
#regex_flavor = advanced    # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false

Hartmut Raschick <hartmut.raschick@ke-elektronik.de> writes:
> [ TRUNCATE is much slower in 7.4 than in 7.3 ]

After looking into this, I think this is because when Rod Taylor
reimplemented TRUNCATE to make it transaction-safe, he essentially
turned it into a variant of CLUSTER.  It is slow because it is creating
and deleting dummy tables and indexes.  I think this is not really
necessary and it could be done better while still being
transaction-safe.  All we really need is to create a new empty table
file, update the table's pg_class row with the new relfilenode, mark
the old file for deletion, and then run REINDEX TABLE (which will
perform similar shenanigans with the indexes).

            regards, tom lane