Quick Links

Re: speeding up a join query that utilizes a view

From:	Kirk Wythers <kwythers(at)umn(dot)edu>
To:	Igor Neyman <ineyman(at)perceptron(dot)com>
Cc:	Kirk Wythers <kwythers(at)umn(dot)edu>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject:	Re: speeding up a join query that utilizes a view
Date:	2013-01-18 15:50:11
Message-ID:	E0E1E6A6-9A18-41C8-B579-013D278DCB82@umn.edu
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

On Jan 18, 2013, at 8:10 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> Yes, my mistake, I meant to ask about fifteen_min_stacked_view definition, and Postgres parameters from postgresql.conf configuration file, at least those - modified from default setting and related to "resource consumption" and "query tuning".
>
> Regards,
> Igor Neyman

Here some extra bits form the postgresql.conf file. As you can see, I have not changed much from the default settings.

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

# - Memory -

shared_buffers = 3GB # 7GB # min 128kB
# (change requires restart)
temp_buffers = 80MB # 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 = 64MB #8MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - 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

#------------------------------------------------------------------------------
# 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 = 6GB #13GB

# - 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

Here is a snip from earlier that includes info about both the table that is used to build the view and the view. In short, I use the UNNEST function to un-pivot all the variables of interest in the "fifteen_min" table into the columns "variable" and "value" in the "fifteen_min_stacked_proper" view.

Thanks again.

Kirk

b4warmed3=# SELECT COUNT(*) FROM fifteen_min; count
---------
1798711
(1 row)

b4warmed3=# SELECT COUNT(*) FROM fifteen_min_stacked_propper;
count
-----------
428093218
(1 row)

In response to

Re: speeding up a join query that utilizes a view at 2013-01-18 14:10:27 from Igor Neyman

Responses

Re: speeding up a join query that utilizes a view at 2013-01-18 16:05:28 from Igor Neyman

Browse pgsql-general by date

	From	Date	Subject
Next Message	Tom Lane	2013-01-18 15:58:22	Re: Update rule on a view - what am I doing wrong
Previous Message	Tom Lane	2013-01-18 14:51:54	Re: String comparison and the SQL standard