Re: Any better plan for this query?..

From: Matthew Wakeling
Subject: Re: Any better plan for this query?..
Date: ,
Msg-id: alpine.DEB.2.00.0905191250380.2341@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: Any better plan for this query?..  (Simon Riggs)
Responses: Re: Any better plan for this query?..  (Simon Riggs)
Re: Any better plan for this query?..  (Scott Carey)
List: pgsql-performance

Tree view

Any better plan for this query?..  (Dimitri, )
 Re: Any better plan for this query?..  (Craig Ringer, )
  Re: Any better plan for this query?..  (Dimitri, )
   Re: Any better plan for this query?..  (Chris, )
    Re: Any better plan for this query?..  (Dimitri, )
     Re: Any better plan for this query?..  (Richard Huxton, )
     Re: Any better plan for this query?..  (Craig Ringer, )
      Re: Any better plan for this query?..  (Kenneth Marshall, )
       Re: Any better plan for this query?..  (Matthew Wakeling, )
      Re: Any better plan for this query?..  (Dimitri, )
  Re: Any better plan for this query?..  (Kenneth Marshall, )
 Re: Any better plan for this query?..  (Heikki Linnakangas, )
  Re: Any better plan for this query?..  (Dimitri, )
   Re: Any better plan for this query?..  (Simon Riggs, )
    Re: Any better plan for this query?..  (Dimitri, )
     Re: Any better plan for this query?..  (Merlin Moncure, )
      Re: Any better plan for this query?..  (Dimitri, )
       Re: Any better plan for this query?..  (Simon Riggs, )
        Re: Any better plan for this query?..  (Dimitri, )
         Re: Any better plan for this query?..  (Tom Lane, )
          Re: Any better plan for this query?..  (Dimitri, )
          Re: Any better plan for this query?..  (Simon Riggs, )
         Re: Any better plan for this query?..  (Merlin Moncure, )
         Re: Any better plan for this query?..  (Simon Riggs, )
          Re: Any better plan for this query?..  (Dimitri, )
         Re: Any better plan for this query?..  (Aidan Van Dyk, )
          Re: Any better plan for this query?..  (Dimitri, )
           Re: Any better plan for this query?..  (Alvaro Herrera, )
            Re: Any better plan for this query?..  (Dimitri, )
             Re: Any better plan for this query?..  (Dimitri Fontaine, )
              Re: Any better plan for this query?..  (Dimitri, )
               Re: Any better plan for this query?..  (Simon Riggs, )
                Re: Any better plan for this query?..  (Dimitri, )
                 Re: Any better plan for this query?..  (Simon Riggs, )
               Re: Any better plan for this query?..  (Heikki Linnakangas, )
                Re: Any better plan for this query?..  (Dimitri, )
                 Re: Any better plan for this query?..  (Simon Riggs, )
                  Re: Any better plan for this query?..  (Dimitri, )
                   Re: Any better plan for this query?..  (Simon Riggs, )
                    Re: Any better plan for this query?..  (Dimitri, )
                     Re: Any better plan for this query?..  (Simon Riggs, )
                      Re: Any better plan for this query?..  ("Dave Dutcher", )
                       Re: Any better plan for this query?..  (Dimitri, )
                        Re: Any better plan for this query?..  (Merlin Moncure, )
                         Re: Any better plan for this query?..  (Dimitri, )
                          Re: Any better plan for this query?..  (Merlin Moncure, )
                           Re: Any better plan for this query?..  (Dimitri, )
                            Re: Any better plan for this query?..  (Merlin Moncure, )
                      Re: Any better plan for this query?..  (Dimitri, )
                       Re: Any better plan for this query?..  (Simon Riggs, )
                        Re: Any better plan for this query?..  (Dimitri, )
                         Re: Any better plan for this query?..  (Dimitri, )
                          Re: Any better plan for this query?..  (Simon Riggs, )
                           Re: Any better plan for this query?..  (Dimitri, )
                      Re: Any better plan for this query?..  (Tom Lane, )
                       Re: Any better plan for this query?..  (Dimitri, )
                       Re: Any better plan for this query?..  (Simon Riggs, )
                        Re: Any better plan for this query?..  (Matthew Wakeling, )
                         Re: Any better plan for this query?..  (Simon Riggs, )
                          Re: Any better plan for this query?..  (Simon Riggs, )
                          Re: Any better plan for this query?..  (Matthew Wakeling, )
                           Re: Any better plan for this query?..  (Simon Riggs, )
                           Re: Any better plan for this query?..  (Scott Carey, )
                          Re: Any better plan for this query?..  (Robert Haas, )
                        Re: Any better plan for this query?..  (Tom Lane, )
                         Re: Any better plan for this query?..  (Simon Riggs, )
                          Re: Any better plan for this query?..  (Tom Lane, )
                           Re: Any better plan for this query?..  (Robert Haas, )
                            Re: Any better plan for this query?..  (Simon Riggs, )
                             Re: Any better plan for this query?..  (Robert Haas, )
                              Re: Any better plan for this query?..  (Simon Riggs, )
                     Re: Any better plan for this query?..  (Scott Carey, )
                      Re: Any better plan for this query?..  (Dimitri, )
                       Re: Any better plan for this query?..  (Scott Carey, )
                        Re: Any better plan for this query?..  (Dimitri, )
                         Re: Any better plan for this query?..  (Scott Carey, )
                          Re: Any better plan for this query?..  (Dimitri, )
               Re: Any better plan for this query?..  (Simon Riggs, )
                Re: Any better plan for this query?..  (Dimitri, )
                 Re: Any better plan for this query?..  (Robert Haas, )
                  Re: Any better plan for this query?..  (Dimitri, )
                   Re: Any better plan for this query?..  ("Kevin Grittner", )
                   Re: Any better plan for this query?..  ("Joshua D. Drake", )
                    Re: Any better plan for this query?..  (Dimitri, )
                   Re: Any better plan for this query?..  (Robert Haas, )
                    Re: Any better plan for this query?..  (Dimitri, )
               Re: Any better plan for this query?..  (Stefan Kaltenbrunner, )
                Re: Any better plan for this query?..  (Dimitri, )
                 Re: Any better plan for this query?..  (Stefan Kaltenbrunner, )
                  Re: Any better plan for this query?..  (Matthew Wakeling, )
                   Re: Any better plan for this query?..  (Simon Riggs, )
                    Re: Any better plan for this query?..  (Matthew Wakeling, )
                     Re: Any better plan for this query?..  (Stefan Kaltenbrunner, )
                     Re: Any better plan for this query?..  (Tom Lane, )
                      Re: Any better plan for this query?..  (Robert Haas, )
                  Re: Any better plan for this query?..  (Dimitri, )
   Re: Any better plan for this query?..  ("Joshua D. Drake", )
           Re: Any better plan for this query?..  (Heikki Linnakangas, )
            Re: Any better plan for this query?..  (Dimitri, )
           Re: Any better plan for this query?..  (Andres Freund, )
            Re: Any better plan for this query?..  (Alvaro Herrera, )
             Re: Any better plan for this query?..  (Scott Carey, )
              Re: Any better plan for this query?..  (Dimitri, )
               Re: Any better plan for this query?..  (Scott Carey, )
                Re: Any better plan for this query?..  (Dimitri Fontaine, )
                 Re: Any better plan for this query?..  (Dimitri, )
                 Re: Any better plan for this query?..  (Simon Riggs, )
                Re: Any better plan for this query?..  (Dimitri, )
    Re: Any better plan for this query?..  (Gregory Stark, )
     Re: Any better plan for this query?..  (Simon Riggs, )
  Re: Any better plan for this query?..  (Matthew Wakeling, )
  Re: Any better plan for this query?..  (Tom Lane, )
   Re: Any better plan for this query?..  (Greg Stark, )
   Re: Any better plan for this query?..  (Robert Haas, )
  Re: Any better plan for this query?..  (Simon Riggs, )
   Re: Any better plan for this query?..  ("Joshua D. Drake", )
    Re: Any better plan for this query?..  (Aidan Van Dyk, )
     Re: Any better plan for this query?..  (Stephen Frost, )
   Re: Any better plan for this query?..  (Robert Haas, )
   Re: Any better plan for this query?..  (Glenn Maynard, )
    Re: Any better plan for this query?..  (Dimitri, )
    Re: Any better plan for this query?..  ("Kevin Grittner", )
     Re: Any better plan for this query?..  (Dimitri, )
      Re: Any better plan for this query?..  ("Kevin Grittner", )
  Re: Any better plan for this query?..  (Dimitri Fontaine, )
   Re: Any better plan for this query?..  (Alvaro Herrera, )
  Re: Any better plan for this query?..  (Robert Haas, )
   Re: Any better plan for this query?..  (Dimitri, )
 Re: Any better plan for this query?..  (Dimitri, )
  Re: Any better plan for this query?..  (Richard Huxton, )
  Re: Any better plan for this query?..  ("Albe Laurenz", )
   Re: Any better plan for this query?..  (Dimitri, )
    Re: Any better plan for this query?..  (Kenneth Marshall, )
 Re: Any better plan for this query?..  (Merlin Moncure, )
  Re: Any better plan for this query?..  (Merlin Moncure, )
   Re: Any better plan for this query?..  (Dimitri, )
    Re: Any better plan for this query?..  (Richard Huxton, )
     Re: Any better plan for this query?..  (Ries van Twisk, )
      Re: Any better plan for this query?..  (Dimitri, )
 Re: Any better plan for this query?..  (Kenneth Marshall, )
  Re: Any better plan for this query?..  (Dimitri, )
   Re: Any better plan for this query?..  (Kenneth Marshall, )
 Re: Any better plan for this query?..  (Dimitri, )
  Re: Any better plan for this query?..  (Alvaro Herrera, )
 Re: Any better plan for this query?..  (Simon Riggs, )

On Tue, 19 May 2009, Simon Riggs wrote:
>> Speaking of avoiding large sorts, I'd like to push again for partial
>> sorts. This is the situation where an index provides data sorted by
>> column "a", and the query requests data sorted by "a, b". Currently,
>> Postgres sorts the entire data set, whereas it need only group each
>> set of identical "a" and sort each by "b".
>
> Partially sorted data takes much less effort to sort (OK, not zero, I
> grant) so this seems like a high complexity, lower value feature. I
> agree it should be on the TODO, just IMHO at a lower priority than some
> other features.

Not arguing with you, however I'd like to point out that partial sorting
allows the results to be streamed, which would lower the cost to produce
the first row of results significantly, and reduce the amount of RAM used
by the query, and prevent temporary tables from being used. That has to be
a fairly major win. Queries with a LIMIT would see the most benefit.

That leads me on to another topic. Consider the query:

SELECT * FROM table ORDER BY a, b

where the column "a" is declared UNIQUE and has an index. Does Postgres
eliminate "b" from the ORDER BY, and therefore allow fetching without
sorting from the index?

Or how about this query:

SELECT * FROM table1, table2 WHERE table1.fk = table2.id ORDER BY
     table1.id, table2.id

where both "id" columns are UNIQUE with an index. Do we eliminate
"table2.id" from the ORDER BY in this case?

Matthew

--
"Programming today is a race between software engineers striving to build
 bigger and better idiot-proof programs, and the Universe trying to produce
 bigger and better idiots. So far, the Universe is winning."  -- Rich Cook


pgsql-performance by date:

From: Merlin Moncure
Date:
Subject: Re: Any better plan for this query?..
From: Dimitri
Date:
Subject: Re: Any better plan for this query?..