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 the help!

Damon


On Mon, Mar 3, 2014 at 8:20 PM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
On Mon, Mar 3, 2014 at 10:12 PM, Damon Snyder <damon@xxxxxxxxxxxxxxx> wrote:
>
>> 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.
>
> The application requires that we find an element and it's neighbors within a
> sorted set at a given offset after filtering by category and status. In the
> examples provided, we need position 50000, 6 above, and 6 below. Is there a
> way do to that more efficiently without first determining the position of
> each element within the set using a window function? How would a subquery
> help?
>
> The only solution I could come up with was to materialize the intermediate
> result with the CTE (since you don't know ahead of time how many objects
> match the status and category criteria) then use the window to include the
> position or index.


You're materializing on a per-query basis. That's no good (as your
timings show). Try to find a way to materialize on a more permanent
basis.

I cannot give you a specific solution without investing way more time
than I would. But consider this: all your queries costs are CPU costs.
You need a better algorithm, or better hardware. I doubt you'll find
hardware that performs 16 times faster, so you have to concentrate on
a better algorithm.

And it's unlikely you'll find a better algorithm without a better data
structure. So you need to reorganize your database to make it easier
to query. I don't think simple SQL optimizations will get you to your
performance goal.


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

  Powered by Linux