Re: Slow query: select * order by XXX desc offset 10 limit 10

Hello Bill and others,

On Thu, Oct 13, 2011 at 4:09 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> In response to Alexander Farber <alexander(dot)farber(at)gmail(dot)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