#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    5
    Rep Power
    0

    My over query is too slow. how to index


    How to index following sql. It is too slow.
    select * from(
    select sum(1) over (partition by code order by date desc) as rown,code, date, last,ref as yesterday_price from timestampdata ) as dsd where rown =1
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Please read this:

    https://wiki.postgresql.org/wiki/Slow_Query_Questions

    and then enhance your question accordingly.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You have a SUM(1) over an ordered partition and you select only the values of 1, so I'm guessing that you only want to get the first record per "code"?
    Then try RANK() instead of SUM(1) because SUM(1) cannot be predicted by the database, RANK() can (using the indexes on code and date).

IMN logo majestic logo threadwatch logo seochat tools logo