Re: Slow query: select * order by XXX desc offset 10 limit 10 - Mailing list pgsql-general

Hello Bill and others,

On Thu, Oct 13, 2011 at 4:09 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to Alexander Farber <alexander.farber@gmail.com>:
>> I use PostgreSQL 8.4.7 on CentOS 6.0 / 64 bit machine
>> with Quad-Core AMD Opteron(tm) Processor 2352 and
>> 16 GB RAM and use it for 1 PHP script - which selects
>> and displays data in jQuery DataTables (i.e. an
>> HTML-table which can be viewed page by page).
>>
>> My problem is, that select's are very slow and
>> using my script is no fun despite all the AJAX stuff -
>> which only tries to retrieve "offset X limit Y" records:
>>
>> quincy=> explain select to_char(qdatetime, 'YYYY-MM-DD') as
>> QDATETIME,ID,NAME,CATEGORY,APPSVERSION,OSVERSION,DETAILS,DEVINFO from
>> quincyview where qdatetime <= now() order by QDATETIME desc offset 10
>> limit 10;
>>
>>
>>       QUERY PLAN
>>
>>
>>
----------------------------------------------------------------------------------------------------------------------
>>
----------------------------------------------------------------------------------------------------------------------
>>
----------------------------------------------------------------------------------------------------------------------
>> ----------------------------------------------
>>  Limit  (cost=600344.67..600344.70 rows=10 width=1172)
>>    ->  Sort  (cost=600344.65..602859.16 rows=1005804 width=1172)
>>          Sort Key: (to_char(quincyview.qdatetime, 'YYYY-MM-DD'::text))
>>          ->  Subquery Scan quincyview  (cost=518261.35..573580.57
>> rows=1005804 width=1172)
>>                ->  Unique  (cost=518261.35..561008.02 rows=1005804 width=252)
>>                      ->  Sort  (cost=518261.35..520775.86 rows=1005804
>> width=252)
>>                            Sort Key: quincynoreset.qdatetime,
>> quincynoreset.id, quincynoreset.name, quincynoreset.cate
>> gory, quincynoreset.appsversion, quincynoreset.osversion,
>> quincynoreset.beta_prog, quincynoreset.catinfo, quincynorese
>> t.details, quincynoreset.devinfo, quincynoreset.email,
>> quincynoreset.emailid, quincynoreset.imei, quincynoreset.pin, q
>> uincynoreset.formfactor, quincynoreset.copied
>>                            ->  Append  (cost=0.00..57003.60
>> rows=1005804 width=252)
>>                                  ->  Seq Scan on quincynoreset
>> (cost=0.00..40011.20 rows=863394 width=242)
>>                                        Filter: (qdatetime <= now())
>>                                  ->  Seq Scan on quincytrack
>> (cost=0.00..6934.36 rows=142410 width=312)
>>                                        Filter: (qdatetime <= now())
>>
>
> #1 Add indexes on qdatetime on both tables
> #2 don't try to order/filter by a calculated value.  Instead modify the
>   query to order and filter by the raw timestamptz column, which will
>   allow that to be done without converting it all to text first.  This
>   will require you to change your aliasing in your query.
>
> A possible solution to #2:
> select to_char(qdatetime, 'YYYY-MM-DD') as QDATETIMEFORMATTED,
>  ID,NAME,CATEGORY,APPSVERSION,OSVERSION,DETAILS,DEVINFO
> from quincyview
> where qdatetime <= now()
> order by QDATETIME desc
> offset 10 limit 10;

I've added 3 new indices on both tables:


quincy=> \d quincynoreset
               Table "public.quincynoreset"
   Column    |            Type             |   Modifiers
-------------+-----------------------------+---------------
 appsversion | character varying(30)       |
 beta_prog   | character varying(20)       |
 category    | character varying(120)      |
 catinfo     | character varying(120)      |
 details     | character varying(50)       |
 devinfo     | character varying(4000)     |
 emailid     | character varying(16)       |
 email       | character varying(320)      |
 formfactor  | character varying(10)       |
 id          | character varying(20)       | not null
 imei        | character varying(25)       |
 name        | character varying(20)       |
 osversion   | character varying(30)       |
 pin         | character varying(12)       |
 qdatetime   | timestamp without time zone |
 copied      | timestamp without time zone | default now()
Indexes:
    "quincynoreset_pkey" PRIMARY KEY, btree (id)
    "quincynoreset_appsversion_index" btree (appsversion)
    "quincynoreset_osversion_index" btree (osversion)
    "quincynoreset_qdatetime_index" btree (qdatetime)

And in my query I've renamed the string column to
QDATETIME_2 (if I've got your suggestion #2 correctly) -
still no visible improvement:


quincy=> explain select to_char(qdatetime, 'YYYY-MM-DD') as
QDATETIME,ID,NAME,CATEGORY,APPSVERSION,OSVERSION,DETAILS,DEVINFO from
quincyview where qdatetime <= now() order by QDATETIME desc offset 10
limit 10;


      QUERY PLAN


----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------
 Limit  (cost=558551.88..558551.91 rows=10 width=1172)
   ->  Sort  (cost=558551.86..560883.79 rows=932773 width=1172)
         Sort Key: (to_char(quincyview.qdatetime, 'YYYY-MM-DD'::text))
         ->  Subquery Scan quincyview  (cost=482428.59..533731.10
rows=932773 width=1172)
               ->  Unique  (cost=482428.59..522071.44 rows=932773 width=252)
                     ->  Sort  (cost=482428.59..484760.52 rows=932773 width=252)
                           Sort Key: quincynoreset.qdatetime,
quincynoreset.id, quincynoreset.name, quincynoreset.cate
gory, quincynoreset.appsversion, quincynoreset.osversion,
quincynoreset.beta_prog, quincynoreset.catinfo, quincynorese
t.details, quincynoreset.devinfo, quincynoreset.email,
quincynoreset.emailid, quincynoreset.imei, quincynoreset.pin, q
uincynoreset.formfactor, quincynoreset.copied
                           ->  Append  (cost=0.00..55177.71
rows=932773 width=252)
                                 ->  Seq Scan on quincynoreset
(cost=0.00..39171.89 rows=807446 width=242)
                                       Filter: (qdatetime <= now())
                                 ->  Seq Scan on quincytrack
(cost=0.00..6678.09 rows=125327 width=315)
                                       Filter: (qdatetime <= now())
(12 rows)

(XXX same query below but with QDATETIME_2 as column name XXX):

quincy=> explain select to_char(qdatetime, 'YYYY-MM-DD') as
QDATETIME_2,ID,NAME,CATEGORY,APPSVERSION,OSVERSION,DETAILS,DEVINFO
from quincyview where qdatetime <= now() order by QDATETIME desc
offset 10 limit 10;


      QUERY PLAN


----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------
 Limit  (cost=558551.88..558551.91 rows=10 width=1172)
   ->  Sort  (cost=558551.86..560883.79 rows=932773 width=1172)
         Sort Key: quincyview.qdatetime
         ->  Subquery Scan quincyview  (cost=482428.59..533731.10
rows=932773 width=1172)
               ->  Unique  (cost=482428.59..522071.44 rows=932773 width=252)
                     ->  Sort  (cost=482428.59..484760.52 rows=932773 width=252)
                           Sort Key: quincynoreset.qdatetime,
quincynoreset.id, quincynoreset.name, quincynoreset.cate
gory, quincynoreset.appsversion, quincynoreset.osversion,
quincynoreset.beta_prog, quincynoreset.catinfo, quincynorese
t.details, quincynoreset.devinfo, quincynoreset.email,
quincynoreset.emailid, quincynoreset.imei, quincynoreset.pin, q
uincynoreset.formfactor, quincynoreset.copied
                           ->  Append  (cost=0.00..55177.71
rows=932773 width=252)
                                 ->  Seq Scan on quincynoreset
(cost=0.00..39171.89 rows=807446 width=242)
                                       Filter: (qdatetime <= now())
                                 ->  Seq Scan on quincytrack
(cost=0.00..6678.09 rows=125327 width=315)
                                       Filter: (qdatetime <= now())
(12 rows)

Regards
Alex