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

In response to Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:

> 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?

#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;

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/