Re: Help with optimizing a query over hierarchical data

Hi Claudio,
Thanks for responding. Here is the explain (http://explain.depesz.com/s/W3W)
for the ordering by meta container starting on line 192 (
https://gist.github.com/drsnyder/9277054#file-object-ordering-setup-sql-L192
).

Here is the explain (http://explain.depesz.com/s/d1O) for the ordering by
score starting on line 192 (
https://gist.github.com/drsnyder/9277054#file-object-ordering-setup-sql-L216
).

Both of the explains were done with (ANALYZE, BUFFERS).

Thanks for the suggestion regarding de-normalizing. I'll consider that
approach for the score based query.

I've also included the server config changes made from updates to
postgresql.conf on the box that I'm testing on. See below.

Thanks,
Damon

version

--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)

name | current_setting | source
------------------------------+--------------------+----------------------
application_name | psql | client
checkpoint_completion_target | 0.9 | configuration file
checkpoint_segments | 16 | configuration file
DateStyle | ISO, MDY | configuration file
default_tablespace | ssd2 | user
default_text_search_config | pg_catalog.english | configuration file
effective_cache_size | 5632MB | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
log_destination | stderr | configuration file
log_directory | pg_log | configuration file
log_filename | postgresql-%a.log | configuration file
log_line_prefix | %d %m %c %x: | configuration file
log_min_duration_statement | 500ms | configuration file
log_min_error_statement | error | configuration file
log_min_messages | error | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_timezone | UTC | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 480MB | configuration file
max_connections | 80 | configuration file
max_stack_depth | 2MB | environment variable
port | 5432 | command line
shared_buffers | 1920MB | configuration file
TimeZone | UTC | configuration file
wal_buffers | 16MB | configuration file
work_mem | 8MB | configuration file
(32 rows)

On Sat, Mar 1, 2014 at 5:02 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>wrote:

> On Fri, Feb 28, 2014 at 5:01 PM, Damon Snyder <damon(at)huddler-inc(dot)com>
> wrote:
> > The primary query that I'm trying to optimize executes in about 1600ms
> on my
> > laptop and about 800ms on production-like hardware (more for the score
> > version). My target is to get the data fetch down below 100ms if
> possible.
>
> Could you post some explain analyze of those particular queries?
>
> > If you have any suggestions it would be greatly appreciated. Am I missing
> > something obvious? Is there a logically equivalent alternative that
> would be
> > more efficient?
>
> I'd suggest de-normalizing a bit. For instance, why don't you put the
> score right into the object? I'm sure the indirection is hurting.
>