pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimization using order by and limit
14+ messages / 5 participants
[nested] [flat]
* Query optimization using order by and limit
@ 2011-09-21 23:14 Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Viscuso @ 2011-09-21 23:14 UTC (permalink / raw)
To: pgsql-performance
First of all, thank you for taking the time to review my question. After
attending the PostgresOpen conference in Chicago last week, I've been
pouring over explain logs for hours on end and although my system is MUCH
better, I still can't resolve a few issues. Luckily my data is pretty well
structured so solving one issue will likely solve many more so I'll start
with this one.
Version: PostgreSQL 9.1rc1, compiled by Visual C++ build 1500, 64-bit
OS: Windows 7 64-bit
ORM: SQLAlchemy
Postgres table structure: I have daily partitioned tables for each of 4
"core tables" (the tables with the majority of my application's data). Each
daily table inherits from its parent. I do not explicitly define a
REFERENCE between these tables because I cannot guarantee the order in which
the events are inserted into the database, but where there are references,
the referenced row should exist in the other's daily table. The reason I
partitioned the data in this manner is to increase query speed and make it
easy to archive old data. (I'm new to high-end Postgres performance so
there's likely several fundamental flaws in my assumptions. I won't turn
down any recommendation.)
An example of a daily partitioned table follows:
cb=# \d osmoduleloads_2011_09_14;
Table "public.osmoduleloads_2011_09_14"
Column | Type |
Modifiers
-----------------------+-----------------------------+------------------------------------------------------------
guid | numeric(20,0) | not null
osprocess_guid | numeric(20,0) | not null
filepath_guid | numeric(20,0) | not null
firstloadtime | numeric(20,0) | not null
md5hash | bytea | not null
host_guid | numeric(20,0) | default NULL::numeric
process_create_time | numeric(20,0) | default NULL::numeric
process_filepath_guid | numeric(20,0) | default NULL::numeric
event_time | timestamp without time zone | default '2011-09-14
00:00:00'::timestamp without time zone
Indexes:
"osmoduleloads_2011_09_14_pkey" PRIMARY KEY, btree (guid)
"idx_osmoduleloads_2011_09_14_filepath_guid" btree (filepath_guid)
"idx_osmoduleloads_2011_09_14_firstload_time" btree (firstloadtime)
"idx_osmoduleloads_2011_09_14_host_guid" btree (host_guid)
"idx_osmoduleloads_2011_09_14_md5hash" btree (md5hash)
"idx_osmoduleloads_2011_09_14_osprocess_guid" btree (osprocess_guid)
Check constraints:
"osmoduleloads_2011_09_14_event_time_check" CHECK (event_time =
'2011-09-14 00:00:00'::timestamp without time zone)
"osmoduleloads_2011_09_14_firstloadtime_check" CHECK (firstloadtime >=
129604464000000000::bigint::numeric AND firstloadtime <
129605328000000000::bigint::numeric)
Inherits: osmoduleloads
Objective: The firstloadtime check constraint ensures that the record is
applicable to that daily table. (In case you were wondering, the large
numerics correspond to the Windows 100-nanosecond since the Epoch.) I'm
inserting millions of records into each daily table so "query slowness" is
quite easy to spot. Given that there is so much data per daily table, I was
hoping to use the order by and limit clauses to "stop out" a query once it
sufficed the limit clause and not be forced to visit each daily table.
However, I'm spending way too much time in the older tables than I'd like -
which leads me to believe that I;m doing something wrong. For ease of
viewing, my explain analyze can be found at http://explain.depesz.com/s/tot
I'm still very new to this so I'm not sure if explain.depesz.com saves the
original query. It wasn't readily apparent that it did so here is the
original query:
SELECT osm_1.*, storefiles_1.*, filepaths_1.*, filepaths_2.* FROM (SELECT *
FROM osmoduleloads JOIN hosts ON hosts.guid = osmoduleloads.host_guid WHERE
hosts.guid = '2007075705813916178' AND osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000 AND
hosts.enabled = true AND hosts.user_id = 111 ORDER BY
osmoduleloads.firstloadtime DESC LIMIT 251) AS osm_1 LEFT OUTER JOIN
storefiles AS storefiles_1 ON osm_1.md5hash = storefiles_1.md5hash LEFT
OUTER JOIN filepaths AS filepaths_1 ON osm_1.process_filepath_guid =
filepaths_1.guid AND osm_1.event_time = filepaths_1.event_time LEFT OUTER
JOIN filepaths AS filepaths_2 ON osm_1.filepath_guid = filepaths_2.guid AND
osm_1.event_time= filepaths_2.event_time ORDER BY osm_1.firstloadtime DESC;
Hopefully my assumptions about order by and limit are correct and this query
can be optimized.
Again, appreciate any help you can lend. Thanks in advance.
Mike
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
@ 2011-09-22 01:48 ` Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Greg Smith @ 2011-09-22 01:48 UTC (permalink / raw)
To: pgsql-performance
On 09/21/2011 07:14 PM, Michael Viscuso wrote:
> Check constraints:
> "osmoduleloads_2011_09_14_event_time_check" CHECK (event_time =
> '2011-09-14 00:00:00'::timestamp without time zone)
> "osmoduleloads_2011_09_14_firstloadtime_check" CHECK
> (firstloadtime >= 129604464000000000::bigint::numeric AND
> firstloadtime < 129605328000000000::bigint::numeric)
> Inherits: osmoduleloads
That weird casting can't be helping. I'm not sure if it's your problem
here, but the constraint exclusion code is pretty picky about matching
the thing you're looking for against the CHECK constraint, and this is a
messy one. The bigint conversion in the middle there isn't doing
anything useful for you anyway; you really should simplify this to just
look like this:
firstloadtime >= 129604464000000000::numeric
> SELECT osm_1.*, storefiles_1.*, filepaths_1.*, filepaths_2.* FROM
> (SELECT * FROM osmoduleloads JOIN hosts ON hosts.guid =
> osmoduleloads.host_guid WHERE hosts.guid = '2007075705813916178' AND
> osmoduleloads.firstloadtime >= 129604320000000000 AND
> osmoduleloads.firstloadtime < 129610367990000000 AND hosts.enabled =
> true AND hosts.user_id = 111 ORDER BY osmoduleloads.firstloadtime DESC
> LIMIT 251) AS osm_1 LEFT OUTER JOIN storefiles AS storefiles_1 ON
> osm_1.md5hash = storefiles_1.md5hash LEFT OUTER JOIN filepaths AS
> filepaths_1 ON osm_1.process_filepath_guid = filepaths_1.guid AND
> osm_1.event_time = filepaths_1.event_time LEFT OUTER JOIN filepaths AS
> filepaths_2 ON osm_1.filepath_guid = filepaths_2.guid AND
> osm_1.event_time= filepaths_2.event_time ORDER BY osm_1.firstloadtime
> DESC;
>
What you should start with here is confirming whether or not a simpler
query touches all of the partitions or just the ones you expect it to.
A simpler one like this:
SELECT * FROM osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000;
Would be the place to begin. Once you've got that working, then you can
build up more pieces, and see if one of them results in the query not
excluding partitions anymore or not. I can't figure out if you're
running into a basic error here, where constraint exclusion just isn't
working at all, or if you are only having this problem because the query
is too complicated. Figuring that out will narrow the potential solutions.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
@ 2011-09-22 02:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2011-09-22 02:09 UTC (permalink / raw)
To: Greg Smith <greg@2ndQuadrant.com>; +Cc: pgsql-performance
Greg Smith <greg@2ndQuadrant.com> writes:
> That weird casting can't be helping. I'm not sure if it's your problem
> here, but the constraint exclusion code is pretty picky about matching
> the thing you're looking for against the CHECK constraint, and this is a
> messy one. The bigint conversion in the middle there isn't doing
> anything useful for you anyway; you really should simplify this to just
> look like this:
> firstloadtime >= 129604464000000000::numeric
I have a more aggressive suggestion: change all the numeric(20,0) fields
to bigint. Unless the OP actually needs values wider than 64 bits,
the choice to use numeric is a significant performance penalty for
nothing.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-09-22 02:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 03:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 14+ messages in thread
From: Michael Viscuso @ 2011-09-22 02:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Thanks guys,
First of all, I should have included my postgres.conf file with the
original submission. Sorry about that. It is now attached.
Based on a recommendation, I also should have shown the parent child
relationship between osmoduleloads and its daily partitioned tables. to
reduce clutter, It is at the end of this message.
Taking this one step at a time and taking Greg's second suggestion
first, issuing
select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000;
appears to only query the appropriate daily tables (2011_09_13 through
2011_09_20 - http://explain.depesz.com/s/QCG). So it appears that
constraint_exclusion is working properly. Putting a limit on the query
like:
select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000
limit 251;
has the result that I'd expect to see http://explain.depesz.com/s/O7fZ.
Ordering by firstloadtime AND limiting like:
select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000
order by firstloadtime desc limit 251;
also has the result that I'd expect to see
http://explain.depesz.com/s/RDh.
Adding the hosts join condition to the mix was still OK
http://explain.depesz.com/s/2Ns.
Adding the hosts.enabled condition was still OK
http://explain.depesz.com/s/UYN.
Adding the hosts.user_id = 111 started the descent but it appears to
still be obeying the proper contraint_exclusion that I'd expect, just
with a ton of rows returned from the most recent daily tables
http://explain.depesz.com/s/4WE.
Adding the final condition hosts_guid = '2007075705813916178' is what
ultimately kills it http://explain.depesz.com/s/8zy. By adding the
host_guid, it spends considerably more time in the older tables than
without this condition and I'm not sure why.
Thanks Greg for the recommendation to step through it like that -
hopefully this helps get us closer to a resolution.
Greg/Tom, you are correct, these columns should be modified to whatever
is easiest for Postgres to recognize 64-bit unsigned integers. Would
you still recommend bigint for unsigned integers? I likely read the
wrong documentation that suggested bigint for signed 64-bit integers and
numeric(20) for unsigned 64-bit integers.
Thanks again for all your help! Perhaps 15 hours of pouring over
explain logs will finally pan out!
Mike
cb=# \d+ osmoduleloads;
Table "public.osmoduleloads"
Column | Type |
Modifiers | Storage | Description
-----------------------+-----------------------------+-----------------------+----------+-------------
guid | numeric(20,0) | not
null | main |
osprocess_guid | numeric(20,0) | not
null | main |
filepath_guid | numeric(20,0) | not
null | main |
firstloadtime | numeric(20,0) | not
null | main |
md5hash | bytea | not
null | extended |
host_guid | numeric(20,0) | default
NULL::numeric | main |
process_create_time | numeric(20,0) | default
NULL::numeric | main |
process_filepath_guid | numeric(20,0) | default
NULL::numeric | main |
event_time | timestamp without time zone
| | plain |
Indexes:
"osmoduleloads_pkey" PRIMARY KEY, btree (guid)
Child tables: osmoduleloads_2001_12_31,
osmoduleloads_2010_10_11,
osmoduleloads_2010_10_12,
osmoduleloads_2010_10_13,
osmoduleloads_2011_07_27,
osmoduleloads_2011_08_04,
osmoduleloads_2011_08_05,
osmoduleloads_2011_08_06,
osmoduleloads_2011_08_07,
osmoduleloads_2011_08_08,
osmoduleloads_2011_08_09,
osmoduleloads_2011_08_10,
osmoduleloads_2011_08_11,
osmoduleloads_2011_08_12,
osmoduleloads_2011_08_13,
osmoduleloads_2011_08_14,
osmoduleloads_2011_08_15,
osmoduleloads_2011_08_16,
osmoduleloads_2011_08_17,
osmoduleloads_2011_08_18,
osmoduleloads_2011_08_19,
osmoduleloads_2011_08_20,
osmoduleloads_2011_08_21,
osmoduleloads_2011_08_22,
osmoduleloads_2011_08_23,
osmoduleloads_2011_08_24,
osmoduleloads_2011_08_25,
osmoduleloads_2011_08_26,
osmoduleloads_2011_08_27,
osmoduleloads_2011_08_28,
osmoduleloads_2011_08_29,
osmoduleloads_2011_08_30,
osmoduleloads_2011_08_31,
osmoduleloads_2011_09_01,
osmoduleloads_2011_09_02,
osmoduleloads_2011_09_03,
osmoduleloads_2011_09_04,
osmoduleloads_2011_09_05,
osmoduleloads_2011_09_06,
osmoduleloads_2011_09_07,
osmoduleloads_2011_09_08,
osmoduleloads_2011_09_09,
osmoduleloads_2011_09_10,
osmoduleloads_2011_09_11,
osmoduleloads_2011_09_12,
osmoduleloads_2011_09_13,
osmoduleloads_2011_09_14,
osmoduleloads_2011_09_15,
osmoduleloads_2011_09_16,
osmoduleloads_2011_09_17,
osmoduleloads_2011_09_18,
osmoduleloads_2011_09_19,
osmoduleloads_2011_09_20,
osmoduleloads_2011_12_01
Has OIDs: no
On 9/21/2011 10:09 PM, Tom Lane wrote:
> Greg Smith <greg@2ndQuadrant.com> writes:
>> That weird casting can't be helping. I'm not sure if it's your problem
>> here, but the constraint exclusion code is pretty picky about matching
>> the thing you're looking for against the CHECK constraint, and this is a
>> messy one. The bigint conversion in the middle there isn't doing
>> anything useful for you anyway; you really should simplify this to just
>> look like this:
>> firstloadtime >= 129604464000000000::numeric
> I have a more aggressive suggestion: change all the numeric(20,0) fields
> to bigint. Unless the OP actually needs values wider than 64 bits,
> the choice to use numeric is a significant performance penalty for
> nothing.
>
> regards, tom lane
>
Attachments:
postgresql.conf (text/plain, 18.9K) download
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '127.0.0.1' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 54320 # (change requires restart)
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 512MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 16MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
shared_preload_libraries = 'auto_explain' # (change requires restart)
custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = '10'
auto_explain.log_analyze = 'on'
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 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 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
synchronous_commit = off # synchronization level; on, off, or local
wal_sync_method = open_datasync #fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1h # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Master Server -
# These settings are ignored on a standby server
#max_wal_senders = 0 # max number of walsender processes
# (change requires restart)
#wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
#replication_timeout = 60s # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
# - Standby Servers -
# These settings are ignored on a master server
#hot_standby = off # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#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
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 3062MB
# - 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
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
#log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
#log_truncate_on_rotation = off # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
#log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
#log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#silent_mode = off # Run server silently.
# DO NOT USE without syslog or
# logging_collector
# (change requires restart)
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
log_checkpoints = on
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%m - %p ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'GMT' #(defaults to server environment setting)'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'English_United States.1252' # locale for system error message
# strings
lc_monetary = 'English_United States.1252' # locale for monetary formatting
lc_numeric = 'English_United States.1252' # locale for number formatting
lc_time = 'English_United States.1252' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
deadlock_timeout = 600s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
escape_string_warning = off
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
@ 2011-09-22 03:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 13:41 ` Re: Query optimization using order by and limit ktm@rice.edu <ktm@rice.edu>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2011-09-22 03:22 UTC (permalink / raw)
To: Michael Viscuso <michael.viscuso@getcarbonblack.com>; +Cc: Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Michael Viscuso <michael.viscuso@getcarbonblack.com> writes:
> Greg/Tom, you are correct, these columns should be modified to whatever
> is easiest for Postgres to recognize 64-bit unsigned integers. Would
> you still recommend bigint for unsigned integers? I likely read the
> wrong documentation that suggested bigint for signed 64-bit integers and
> numeric(20) for unsigned 64-bit integers.
Unsigned? Oh, hm, that's a bit of a problem because we don't have any
unsigned types. If you really need to go to 2^64 and not 2^63 then
you're stuck with numeric ... but that last bit is costing ya a lot.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 03:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-09-22 13:41 ` ktm@rice.edu <ktm@rice.edu>
2011-09-22 13:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
0 siblings, 1 reply; 14+ messages in thread
From: ktm@rice.edu @ 2011-09-22 13:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
On Wed, Sep 21, 2011 at 11:22:53PM -0400, Tom Lane wrote:
> Michael Viscuso <michael.viscuso@getcarbonblack.com> writes:
> > Greg/Tom, you are correct, these columns should be modified to whatever
> > is easiest for Postgres to recognize 64-bit unsigned integers. Would
> > you still recommend bigint for unsigned integers? I likely read the
> > wrong documentation that suggested bigint for signed 64-bit integers and
> > numeric(20) for unsigned 64-bit integers.
>
> Unsigned? Oh, hm, that's a bit of a problem because we don't have any
> unsigned types. If you really need to go to 2^64 and not 2^63 then
> you're stuck with numeric ... but that last bit is costing ya a lot.
>
> regards, tom lane
>
Hi Michael,
If you have access to the application, you can map the unsigned 64-bits
to the PostgreSQL signed 64-bit type with a simple subtraction. That will
allow you to drop all the numeric use. Also if the guid is a 64-bit
values stuffed into a numeric(20), you can do it there as well. I achieved
a hefty performance boost by making those application level changes in a
similar situation.
Regards,
Ken
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 03:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 13:41 ` Re: Query optimization using order by and limit ktm@rice.edu <ktm@rice.edu>
@ 2011-09-22 13:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Viscuso @ 2011-09-22 13:55 UTC (permalink / raw)
To: ktm@rice.edu <ktm@rice.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Thanks Ken,
I'm discussing with my coworker how to best make that change *as we
speak*. Do you think this will also resolve the original issue I'm
seeing where the query doesn't "limit out properly" and spends time in
child tables that won't yield any results? I was hoping that by using
the check constraints, I could query over a week or month's worth of
partitioned tables and the combination of order by and limit would
eliminate any time searching unnecessary tables but that doesn't appear
to be true. (I'm still very new to high-end Postgres performance so I
could be mistaken.)
Regardless, in the meantime, I'll switch those columns to bigint instead
of numeric and have an update as soon as possible.
Thanks for your help!
Mike
On 9/22/2011 9:41 AM, ktm@rice.edu wrote:
> On Wed, Sep 21, 2011 at 11:22:53PM -0400, Tom Lane wrote:
>> Michael Viscuso <michael.viscuso@getcarbonblack.com> writes:
>>> Greg/Tom, you are correct, these columns should be modified to whatever
>>> is easiest for Postgres to recognize 64-bit unsigned integers. Would
>>> you still recommend bigint for unsigned integers? I likely read the
>>> wrong documentation that suggested bigint for signed 64-bit integers and
>>> numeric(20) for unsigned 64-bit integers.
>> Unsigned? Oh, hm, that's a bit of a problem because we don't have any
>> unsigned types. If you really need to go to 2^64 and not 2^63 then
>> you're stuck with numeric ... but that last bit is costing ya a lot.
>>
>> regards, tom lane
>>
> Hi Michael,
>
> If you have access to the application, you can map the unsigned 64-bits
> to the PostgreSQL signed 64-bit type with a simple subtraction. That will
> allow you to drop all the numeric use. Also if the guid is a 64-bit
> values stuffed into a numeric(20), you can do it there as well. I achieved
> a hefty performance boost by making those application level changes in a
> similar situation.
>
> Regards,
> Ken
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
@ 2011-09-22 14:53 ` Stephen Frost <sfrost@snowman.net>
2011-09-22 22:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-25 22:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 14+ messages in thread
From: Stephen Frost @ 2011-09-22 14:53 UTC (permalink / raw)
To: Michael Viscuso <michael.viscuso@getcarbonblack.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
* Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
> Adding the final condition hosts_guid = '2007075705813916178' is what
> ultimately kills it http://explain.depesz.com/s/8zy. By adding the
> host_guid, it spends considerably more time in the older tables than
> without this condition and I'm not sure why.
What I think is happening here is that PG is pushing down that filter
(not typically a bad thing..), but with that condition, it's going to
scan the index until it finds a match for that filter before returning
back up only to have that result cut out due to the limit. Having it as
numerics isn't helping here, but the bigger issue is having to check all
those tuples for a match to the filter.
Mike, the filter has to be applied before the order by/limit, since
those clauses come after the filter has been applied (you wouldn't want
a 'where x = 2 limit 10' to return early just because it found 10
records where x didn't equal 2).
What would be great is if PG would realize that the CHECK constraints
prevent earlier records from being in these earlier tables, so it
shouldn't need to consider them at all once the records from the
'latest' table has been found and the limit reached (reverse all this
for an 'ascending' query, of course), which we can do when there's no
order by. I don't believe we have that kind of logic or that
information available at this late stage- the CHECK constraints are used
to eliminate the impossible-to-match tables, but that's it.
One option, which isn't great of course, would be to implement your own
'nested loop' construct (something I typically despise..) in the
application which just walks backwards from the latest and pulls
whatever records it can from each day and then stops once it hits the
limit.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
@ 2011-09-22 22:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 23:14 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Viscuso @ 2011-09-22 22:55 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Stephen,
I spent the better part of the day implementing an application layer
nested loop and it seems to be working well. Of course it's a little
slower than a Postgres only solution because it has to pass data back
and forth for each daily table query until it reaches the limit, but at
least I don't have "runaway" queries like I was seeing before. That
should be a pretty good stopgap solution for the time being.
I was really hoping there was a Postgres exclusive answer though! :) If
there are any other suggestions, it's a simple flag in my application to
query the other way again...
Thanks for all your help - and I'm still looking to change those
numerics to bigints, just haven't figured out the best way yet.
Mike
On 9/22/2011 10:53 AM, Stephen Frost wrote:
> * Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
>> Adding the final condition hosts_guid = '2007075705813916178' is what
>> ultimately kills it http://explain.depesz.com/s/8zy. By adding the
>> host_guid, it spends considerably more time in the older tables than
>> without this condition and I'm not sure why.
>
> What I think is happening here is that PG is pushing down that filter
> (not typically a bad thing..), but with that condition, it's going to
> scan the index until it finds a match for that filter before returning
> back up only to have that result cut out due to the limit. Having it as
> numerics isn't helping here, but the bigger issue is having to check all
> those tuples for a match to the filter.
>
> Mike, the filter has to be applied before the order by/limit, since
> those clauses come after the filter has been applied (you wouldn't want
> a 'where x = 2 limit 10' to return early just because it found 10
> records where x didn't equal 2).
>
> What would be great is if PG would realize that the CHECK constraints
> prevent earlier records from being in these earlier tables, so it
> shouldn't need to consider them at all once the records from the
> 'latest' table has been found and the limit reached (reverse all this
> for an 'ascending' query, of course), which we can do when there's no
> order by. I don't believe we have that kind of logic or that
> information available at this late stage- the CHECK constraints are used
> to eliminate the impossible-to-match tables, but that's it.
>
> One option, which isn't great of course, would be to implement your own
> 'nested loop' construct (something I typically despise..) in the
> application which just walks backwards from the latest and pulls
> whatever records it can from each day and then stops once it hits the
> limit.
>
> Thanks,
>
> Stephen
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iQEcBAEBAgAGBQJOe7zzAAoJEBKjVK2HR1IXYwAIAKQBnFOtCNljL1Hs1ZQW3e+I
ele/kZCiHzgHLFpN7zawt1Y7qf+3ntd6u+mkatJsnqeC+HY1Qee4VTUqr+hIKhcc
VIGuuYkzuojs6/PgF6MAERHP24lRFdLCQtMgTY8RshYODvc07VpqkLq1cXhsNJZw
6pNBTEpEmA0MzMrmk3x6C8lFbyXZAYUxNLwG5SEWecV+lkOjnA70oKnSxG6EXRgk
fkj2l1ezVn23KoO8SSUp4xBFHHOY/PQP9JtV7b52Gm5PC7lOqFFrXFygNP0KkWho
TzyjoYKttShEjmTMXoLt181+NB4rQEas8USasemRA1pUkx2NrfvcK46gYucOAsg=
=8yQW
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
2011-09-22 22:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
@ 2011-09-22 23:14 ` Stephen Frost <sfrost@snowman.net>
2011-09-22 23:21 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
0 siblings, 1 reply; 14+ messages in thread
From: Stephen Frost @ 2011-09-22 23:14 UTC (permalink / raw)
To: Michael Viscuso <michael.viscuso@getcarbonblack.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Mike,
* Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
> I spent the better part of the day implementing an application layer
> nested loop and it seems to be working well. Of course it's a little
> slower than a Postgres only solution because it has to pass data back
> and forth for each daily table query until it reaches the limit, but at
> least I don't have "runaway" queries like I was seeing before. That
> should be a pretty good stopgap solution for the time being.
Glad to hear that you were able to get something going which worked for
you.
> I was really hoping there was a Postgres exclusive answer though! :) If
> there are any other suggestions, it's a simple flag in my application to
> query the other way again...
I continue to wonder if some combination of multi-column indexes might
have made the task of finding the 'lowest' record from each of the
tables fast enough that it wouldn't be an issue.
> Thanks for all your help - and I'm still looking to change those
> numerics to bigints, just haven't figured out the best way yet.
Our timestamps are also implemented using 64bit integers and would allow
you to use all the PG date/time functions and operators. Just a
thought.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
2011-09-22 22:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 23:14 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
@ 2011-09-22 23:21 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Viscuso @ 2011-09-22 23:21 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
Stephen,
Yes, I couldn't agree more. The next two things I will be looking at very
carefully are the timestamps and indexes. I will reply to this post if
either dramatically helps.
Thanks again for all your help. My eyes were starting to bleed from staring
at explain logs!
Mike
On Thu, Sep 22, 2011 at 7:14 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Mike,
>
> * Michael Viscuso (michael.viscuso@getcarbonblack.com) wrote:
> > I spent the better part of the day implementing an application layer
> > nested loop and it seems to be working well. Of course it's a little
> > slower than a Postgres only solution because it has to pass data back
> > and forth for each daily table query until it reaches the limit, but at
> > least I don't have "runaway" queries like I was seeing before. That
> > should be a pretty good stopgap solution for the time being.
>
> Glad to hear that you were able to get something going which worked for
> you.
>
> > I was really hoping there was a Postgres exclusive answer though! :) If
> > there are any other suggestions, it's a simple flag in my application to
> > query the other way again...
>
> I continue to wonder if some combination of multi-column indexes might
> have made the task of finding the 'lowest' record from each of the
> tables fast enough that it wouldn't be an issue.
>
> > Thanks for all your help - and I'm still looking to change those
> > numerics to bigints, just haven't figured out the best way yet.
>
> Our timestamps are also implemented using 64bit integers and would allow
> you to use all the PG date/time functions and operators. Just a
> thought.
>
> Thanks,
>
> Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk57wXAACgkQrzgMPqB3kijaNwCfQ9cSdzzHyiPwa+BTzIihWR7T
> baoAoIbL8P3atU1cfbcCoFXFGbKE7fPt
> =ZRqu
> -----END PGP SIGNATURE-----
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
@ 2011-09-25 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-26 00:34 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2011-09-25 22:22 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Stephen Frost <sfrost@snowman.net> writes:
> What I think is happening here is that PG is pushing down that filter
> (not typically a bad thing..), but with that condition, it's going to
> scan the index until it finds a match for that filter before returning
> back up only to have that result cut out due to the limit.
Yeah, it's spending quite a lot of time finding the first matching row
in each child table. I'm curious why that is though; are the child
tables not set up with nonoverlapping firstloadtime ranges?
> What would be great is if PG would realize that the CHECK constraints
> prevent earlier records from being in these earlier tables,
The explain shows that that isn't the case, because it *is* finding at
least one candidate row in each table. It's just running quite far into
the firstloadtime sequence to do it.
If you're stuck with this table arrangement, one thing that would help
is a two-column index on (host_guid, firstloadtime) on each child table.
That would match the search condition exactly, and so reduce the cost
to find the first matching row to nearly nil. Whether this query's
speed is important enough to justify maintaining such an index is a
question I can't answer for you.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
2011-09-25 22:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-09-26 00:34 ` Stephen Frost <sfrost@snowman.net>
2011-09-26 01:24 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Stephen Frost @ 2011-09-26 00:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Yeah, it's spending quite a lot of time finding the first matching row
> in each child table. I'm curious why that is though; are the child
> tables not set up with nonoverlapping firstloadtime ranges?
They are set up w/ nonoverlapping firstloadtime ranges, using CHECK
constraints such as:
"osmoduleloads_2011_09_14_firstloadtime_check" CHECK (firstloadtime >=
129604464000000000::bigint::numeric AND firstloadtime <
129605328000000000::bigint::numeric)
The issue here is that the query is saying "Give me the first 150
records with this host_id in this week-long range". PG happily
eliminates all the tables that are outside of the week-long range during
constraint exclusion. After that, however, it hunts down the earliest
records (which matches 'host_id') from each child table. Sure, from
each table there's a record in the week-long range with the host_id that
matches. What PG doesn't realize is that it can stop after pulling the
150 records from the most recent table (and flipping the direction of
the query or the tables doesn't help- PG still pulls a record from each
table).
> > What would be great is if PG would realize that the CHECK constraints
> > prevent earlier records from being in these earlier tables,
>
> The explain shows that that isn't the case, because it *is* finding at
> least one candidate row in each table. It's just running quite far into
> the firstloadtime sequence to do it.
My point above is that the CHECK constraints ensure an ordering which
could be leveraged to use the latest table first and then stop if enough
tuples are returned (or immediately go to the next table), without ever
considering the other tables. I'm not looking for PG to eliminate those
other tables for consideration in all cases- if the limit is large
enough, it may get all the way down to them. I'm pretty sure this isn't
something which PG does today and I don't expect teaching it to do this
to be trivial, but it certainly would be nice as this strikes me as a
very common use-case.
> If you're stuck with this table arrangement, one thing that would help
> is a two-column index on (host_guid, firstloadtime) on each child table.
Agreed, I mentioned this to the OP previously and it's on his list of
things to try.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query optimization using order by and limit
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Re: Query optimization using order by and limit Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Re: Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
2011-09-25 22:22 ` Re: Query optimization using order by and limit Tom Lane <tgl@sss.pgh.pa.us>
2011-09-26 00:34 ` Re: Query optimization using order by and limit Stephen Frost <sfrost@snowman.net>
@ 2011-09-26 01:24 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2011-09-26 01:24 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Michael Viscuso <michael.viscuso@getcarbonblack.com>; Greg Smith <greg@2ndQuadrant.com>; pgsql-performance
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Yeah, it's spending quite a lot of time finding the first matching row
>> in each child table. I'm curious why that is though; are the child
>> tables not set up with nonoverlapping firstloadtime ranges?
> The issue here is that the query is saying "Give me the first 150
> records with this host_id in this week-long range".
Oh, I see. So the query range overlaps multiple child tables, even
after constraint exclusion eliminates a lot of them.
> My point above is that the CHECK constraints ensure an ordering which
> could be leveraged to use the latest table first and then stop if enough
> tuples are returned (or immediately go to the next table), without ever
> considering the other tables.
Yeah. My opinion is that trying to reverse-engineer that from the CHECK
constraints would cost a lot more than it's worth. What we need, and
will hopefully have sooner or later, is an abstract concept of
"partitioned table" in which this kind of relationship is known a-priori
instead of having to be laboriously re-deduced every time we plan a
query.
>> If you're stuck with this table arrangement, one thing that would help
>> is a two-column index on (host_guid, firstloadtime) on each child table.
> Agreed, I mentioned this to the OP previously and it's on his list of
> things to try.
AFAICS the fact that this example would be fast if we were only paying
attention to the newest table is mere luck. If it can take a long time
to find the first matching host_guid record in several of the child
tables, why might it not take just as long to find said record in the
other one? I think you really need the two-column indexes, if keeping
this query's runtime to a minimum is critical.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2011-09-26 01:24 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-21 23:14 Query optimization using order by and limit Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 01:48 ` Greg Smith <greg@2ndQuadrant.com>
2011-09-22 02:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 02:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 03:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-22 13:41 ` ktm@rice.edu <ktm@rice.edu>
2011-09-22 13:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 14:53 ` Stephen Frost <sfrost@snowman.net>
2011-09-22 22:55 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-22 23:14 ` Stephen Frost <sfrost@snowman.net>
2011-09-22 23:21 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
2011-09-25 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-26 00:34 ` Stephen Frost <sfrost@snowman.net>
2011-09-26 01:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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