Re: Help with optimizing a query over hierarchical data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On Fri, Feb 28, 2014 at 5:01 PM, Damon Snyder <damon@xxxxxxxxxxxxxxx> 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.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux