Hello Bill and others,
On Thu, Oct 13, 2011 at 4:09 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
> In response to Alexander Farber <alexander.farber@xxxxxxxxx>:
>> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
