From: Julius Tuskenis <julius@nsoft.lt>
To: pgsql-performance@postgresql.org
Subject: generating a large XML document
Date: Thu, 16 Jun 2011 22:33:56 +0300
Message-ID: <4DFA5AA4.8090001@nsoft.lt> (raw)
Hello, postgresql guru's
When developing ticket export mechanism for our ticketing system I
thought the wise thing would be to generate the result (XML file) in
stored function. This worked fine for small datasets, but for the larger
ones I see it takes much longer to generate the document than to select
the data needed for it.
Here are several "explain alalyze"s :
explain analyze
SELECT * from tex.fnk_access_control_tickets(8560, 0);
"Function Scan on fnk_access_control_tickets (cost=0.25..10.25
rows=1000 width=246) (actual time=479.276..484.429 rows=16292 loops=1)"
"Total runtime: 486.774 ms"
explain analyze
SELECT
--xmlagg(
XMLELEMENT ( NAME "bar",
XMLELEMENT ( NAME "code", tick_barcode),
XMLELEMENT ( NAME "stat", status),
CASE WHEN type IS NOT NULL THEN XMLELEMENT ( NAME "tip", type)
ELSE NULL END,
CASE WHEN sec_name IS NOT NULL THEN XMLELEMENT ( NAME "sec",
sec_name) ELSE NULL END,
CASE WHEN row_name IS NOT NULL THEN XMLELEMENT ( NAME "row",
row_name) ELSE NULL END,
CASE WHEN seat_name IS NOT NULL THEN XMLELEMENT ( NAME "plc",
seat_name) ELSE NULL END,
CASE WHEN substr(tick_barcode,length(tick_barcode),1)= '1' THEN
XMLELEMENT ( NAME "groups",
XMLELEMENT ( NAME "group", 1)
)
ELSE NULL END
)
-- )
FROM tex.fnk_access_control_tickets(8560, 0);
"Function Scan on fnk_access_control_tickets (cost=0.25..17.75
rows=1000 width=238) (actual time=476.446..924.785 rows=16292 loops=1)"
"Total runtime: 928.768 ms"
explain analyze
SELECT
xmlagg(
XMLELEMENT ( NAME "bar",
XMLELEMENT ( NAME "code", tick_barcode),
XMLELEMENT ( NAME "stat", status),
CASE WHEN type IS NOT NULL THEN XMLELEMENT ( NAME "tip", type)
ELSE NULL END,
CASE WHEN sec_name IS NOT NULL THEN XMLELEMENT ( NAME "sec",
sec_name) ELSE NULL END,
CASE WHEN row_name IS NOT NULL THEN XMLELEMENT ( NAME "row",
row_name) ELSE NULL END,
CASE WHEN seat_name IS NOT NULL THEN XMLELEMENT ( NAME "plc",
seat_name) ELSE NULL END,
CASE WHEN substr(tick_barcode,length(tick_barcode),1)= '1' THEN
XMLELEMENT ( NAME "groups",
XMLELEMENT ( NAME "group", 1)
)
ELSE NULL END
)
)
FROM tex.fnk_access_control_tickets(8560, 0);
"Aggregate (cost=12.75..12.77 rows=1 width=238) (actual
time=16110.847..16110.848 rows=1 loops=1)"
" -> Function Scan on fnk_access_control_tickets (cost=0.25..10.25
rows=1000 width=238) (actual time=500.029..520.974 rows=16292 loops=1)"
"Total runtime: 16111.264 ms"
It seems the aggregate combining the elements is to blame... What I did
next was rewriting it in stored function using FOR loop and combining it
in a text variable. Sadly the result was the same (approximately 16
seconds).
From that I had to conclude that text := text + some_text operation is
an expensive one, but now I have no ideas left how to solve the problem.
Any notices, recommendations, advices are very welcome.
I've also tried google'ing on XML creation in Postgresql, but found no
warnings or even mentioning xmlagg could cause a headache. I have
nowhere to turn for help now, so please advice...
Not sure if that will help, but anyway:
Server:
PostgreSQL 9.0.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.5-10) 4.4.5, 32-bit
see the result of select * from pg_settings in attachment if needed.
Client:
Windows XP, pgAdmin 1.12.3
Thank you in advance.
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
Attachments:pg_settings.txt (text/plain, 5.3K) download
"allow_system_table_mods";"off"
"application_name";"pgAdmin III - Query Tool"
"archive_command";"(disabled)"
"archive_mode";"off"
"archive_timeout";"0"
"array_nulls";"on"
"authentication_timeout";"60"
"autovacuum";"off"
"autovacuum_analyze_scale_factor";"0.1"
"autovacuum_analyze_threshold";"50"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"3"
"autovacuum_naptime";"60"
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"50"
"backslash_quote";"safe_encoding"
"bgwriter_delay";"200"
"bgwriter_lru_maxpages";"100"
"bgwriter_lru_multiplier";"2"
"block_size";"8192"
"bonjour";"off"
"bonjour_name";""
"bytea_output";"escape"
"check_function_bodies";"on"
"checkpoint_completion_target";"0.5"
"checkpoint_segments";"3"
"checkpoint_timeout";"300"
"checkpoint_warning";"30"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"commit_delay";"0"
"commit_siblings";"5"
"constraint_exclusion";"partition"
"cpu_index_tuple_cost";"0.005"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"cursor_tuple_fraction";"0.1"
"custom_variable_classes";""
"DateStyle";"ISO, MDY"
"db_user_namespace";"off"
"deadlock_timeout";"1000"
"debug_assertions";"off"
"debug_pretty_print";"on"
"debug_print_parse";"off"
"debug_print_plan";"off"
"debug_print_rewritten";"off"
"default_statistics_target";"100"
"default_tablespace";""
"default_text_search_config";"pg_catalog.english"
"default_transaction_isolation";"read committed"
"default_transaction_read_only";"off"
"default_with_oids";"off"
"effective_cache_size";"16384"
"effective_io_concurrency";"1"
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"escape_string_warning";"on"
"extra_float_digits";"0"
"from_collapse_limit";"8"
"fsync";"on"
"full_page_writes";"on"
"geqo";"on"
"geqo_effort";"5"
"geqo_generations";"0"
"geqo_pool_size";"0"
"geqo_seed";"0"
"geqo_selection_bias";"2"
"geqo_threshold";"12"
"gin_fuzzy_search_limit";"0"
"hot_standby";"off"
"ignore_system_indexes";"off"
"integer_datetimes";"on"
"IntervalStyle";"postgres"
"join_collapse_limit";"8"
"krb_caseins_users";"off"
"krb_srvname";"postgres"
"lc_collate";"en_US.UTF-8"
"lc_ctype";"en_US.UTF-8"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"
"listen_addresses";"*"
"lo_compat_privileges";"off"
"local_preload_libraries";""
"log_autovacuum_min_duration";"-1"
"log_checkpoints";"off"
"log_connections";"off"
"log_destination";"stderr"
"log_disconnections";"off"
"log_duration";"off"
"log_error_verbosity";"default"
"log_executor_stats";"off"
"log_hostname";"off"
"log_line_prefix";"%h %m %s "
"log_lock_waits";"on"
"log_min_duration_statement";"15000"
"log_min_error_statement";"error"
"log_min_messages";"warning"
"log_parser_stats";"off"
"log_planner_stats";"off"
"log_rotation_age";"1440"
"log_rotation_size";"10240"
"log_statement";"none"
"log_statement_stats";"off"
"log_temp_files";"-1"
"log_timezone";"localtime"
"log_truncate_on_rotation";"off"
"logging_collector";"off"
"maintenance_work_mem";"16384"
"max_connections";"150"
"max_files_per_process";"1000"
"max_function_args";"100"
"max_identifier_length";"63"
"max_index_keys";"32"
"max_locks_per_transaction";"64"
"max_prepared_transactions";"0"
"max_stack_depth";"2048"
"max_standby_archive_delay";"30000"
"max_standby_streaming_delay";"30000"
"max_wal_senders";"0"
"password_encryption";"on"
"plpgsql.variable_conflict";"error"
"port";"5432"
"post_auth_delay";"0"
"pre_auth_delay";"0"
"random_page_cost";"4"
"search_path";"public, tex"
"segment_size";"131072"
"seq_page_cost";"1"
"server_encoding";"UTF8"
"server_version";"9.0.3"
"server_version_num";"90003"
"session_replication_role";"origin"
"shared_buffers";"16384"
"silent_mode";"off"
"sql_inheritance";"on"
"ssl";"off"
"ssl_renegotiation_limit";"524288"
"standard_conforming_strings";"off"
"statement_timeout";"0"
"superuser_reserved_connections";"7"
"synchronize_seqscans";"on"
"synchronous_commit";"on"
"syslog_facility";"local0"
"syslog_ident";"postgres"
"tcp_keepalives_count";"9"
"tcp_keepalives_idle";"7200"
"tcp_keepalives_interval";"75"
"temp_buffers";"1024"
"temp_tablespaces";""
"TimeZone";"localtime"
"timezone_abbreviations";"Default"
"trace_notify";"off"
"trace_recovery_messages";"log"
"trace_sort";"off"
"track_activities";"on"
"track_activity_query_size";"1024"
"track_counts";"on"
"track_functions";"none"
"transaction_isolation";"read committed"
"transaction_read_only";"off"
"transform_null_equals";"off"
"unix_socket_group";""
"unix_socket_permissions";"511"
"update_process_title";"on"
"vacuum_cost_delay";"0"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_defer_cleanup_age";"0"
"vacuum_freeze_min_age";"50000000"
"vacuum_freeze_table_age";"150000000"
"wal_block_size";"8192"
"wal_buffers";"8"
"wal_keep_segments";"0"
"wal_level";"minimal"
"wal_segment_size";"2048"
"wal_sender_delay";"200"
"wal_sync_method";"fdatasync"
"wal_writer_delay";"200"
"work_mem";"1024"
"xmlbinary";"base64"
"xmloption";"content"
"zero_damaged_pages";"off"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: julius@nsoft.lt
Subject: Re: generating a large XML document
In-Reply-To: <4DFA5AA4.8090001@nsoft.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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