Re: Help with optimizing a query over hierarchical data

Um... I think your problem is a misuse of CTE. Your CTE is building an
intermediate of several thousands of rows only to select a dozen
afterwards. You may want to consider a view or subquery, though I'm
not sure pg will be able to optimize much given your use of window
functions, which forces a materialization of that intermediate result.

I think you need to re-think your queries to be smarter about that.

On Mon, Mar 3, 2014 at 2:55 PM, Damon Snyder <damon(at)huddler-inc(dot)com> wrote:
> 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.
>
>