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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hello,

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).

I select records from 1 view which unites 2 identical tables:

quincy=> \d quincyview
               View "public.quincyview"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 qdatetime   | timestamp without time zone |
 id          | character varying(20)       |
 name        | character varying(20)       |
 category    | character varying(120)      |
 appsversion | character varying(30)       |
 osversion   | character varying(30)       |
 beta_prog   | character varying(20)       |
 catinfo     | character varying(120)      |
 details     | character varying(50)       |
 devinfo     | character varying(4000)     |
 email       | character varying(320)      |
 emailid     | character varying(16)       |
 imei        | character varying(25)       |
 pin         | character varying(12)       |
 formfactor  | character varying(10)       |
 copied      | timestamp without time zone |
View definition:
         SELECT quincynoreset.qdatetime, quincynoreset.id,
quincynoreset.name, quincynoreset.category, quincynoreset.appsversion,
quincynoreset.osversion, quincynoreset.beta_prog,
quincynoreset.catinfo, quincynoreset.details, quincynoreset.devinfo,
quincynoreset.email, quincynoreset.emailid, quincynoreset.imei,
quincynoreset.pin, quincynoreset.formfactor, quincynoreset.copied
           FROM quincynoreset
UNION
         SELECT quincytrack.qdatetime, quincytrack.id,
quincytrack.name, quincytrack.category, quincytrack.appsversion,
quincytrack.osversion, quincytrack.beta_prog, quincytrack.catinfo,
quincytrack.details, quincytrack.devinfo, quincytrack.email,
quincytrack.emailid, quincytrack.imei, quincytrack.pin,
quincytrack.formfactor, quincytrack.copied
           FROM quincytrack;

And here is 1 of the 2 tables (the other is same, except its name):

quincy=> \d quincytrack;
                Table "public.quincytrack"
   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:
    "quincytrack_pkey" PRIMARY KEY, btree (id)

There are around 1 mio records in the view:

quincy=> select count(*) from quincyview ;
 count
--------
 950476
(1 row)

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())

Does anybody please have an idea,
how to speed up my select statements?

Regards
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general