Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSvLV-0008UR-CW for pgsql-performance@postgresql.org; Mon, 29 Oct 2012 19:50:01 +0000 Received: from nm3-vm3.bullet.mail.ne1.yahoo.com ([98.138.91.133]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSvLO-00080u-Fc for pgsql-performance@postgresql.org; Mon, 29 Oct 2012 19:50:00 +0000 Received: from [98.138.226.177] by nm3.bullet.mail.ne1.yahoo.com with NNFMP; 29 Oct 2012 19:49:53 -0000 Received: from [98.138.89.192] by tm12.bullet.mail.ne1.yahoo.com with NNFMP; 29 Oct 2012 19:49:53 -0000 Received: from [127.0.0.1] by omp1050.mail.ne1.yahoo.com with NNFMP; 29 Oct 2012 19:49:53 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 26669.16653.bm@omp1050.mail.ne1.yahoo.com Received: (qmail 5985 invoked by uid 60001); 29 Oct 2012 19:49:52 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1351540192; bh=933QWRGzfgSvamWDSyQW4ycDZuPRT/CSztSEGRAcAis=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=Fk/Ox9Bv5jxDQgorHeYXJEx9SMghu1mTpNPYPRTn+PyZBcO3I/r+5byF7Ydx25uOVVnwG68Y9sI1MqtjVNt5LoFRiTEsLo4zhpS13959oU5lAKpKzkmAJ6lhPxUozNFUfVgzBJYP9zuBdbB6TaXl5aRmQzznqrjN+TM+cm0tc10= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=QhcAVeutgO8wVOfNLNd7DP+EcmKVpGrOGBlS1roYezLlkOnxOvg+eUOA95GOXufEJ/Nk9nw0vD1n1kwDXfpmEAYY3P77DYrp9tjZrNkqzBAuBYb7Es/BlES2XQj9nB/dhqG+gDjncTFq9PzfoVmXnBFy0U2wALlg87XSV/7uovg=; X-YMail-OSG: M1ZroWAVM1l21iHAea340jac6BD9kyiTV6XVjXUSlL9s0g4 euN2dvzKkqnd23EX2Ef27C3365EaHzq1B8dxvfz3kEgACaKfkPcCcN3nXQXG wnrQYeCO5PlKG_FKLD3fDjw1MLZru6p0yHZ_xB2NLKrZKGHuuTTooriQxL57 lytAYwrommyiUJJgH5HDebAN3brnBQz443Dy6Js3H7nT5FtBfk7tqGMJVYLf jnp.xVWfn0wWbNusfxY.NFus.YMggajROU2Amyn4wkfQlUznZDQ4wGqZPtqJ YDtaNuqZ.aFkJwSOq6sPnnykUkTkqn2pq8PYTRvazS7S6QOXXYDueLcp9c0p VlQsauNeEzzMDB1VQZuEqmfCiA2q0jTSf.IH1CdKMPY_KBNJWBkVLTXPQ2HI fGedWcSSLW8_KXCqcsFb9RU_WottkfOKWNQx2WUbfYFU9LW2bV8T3jgg9C5D XGAAqZy8Nxr.zKWfndKxSjjtpP4I9UW4shalgjauHJ2B_9Nu3WTIsYsjaKpu Fgn.SyC3Paj0pwAD353fjPXQslAZIGixbvxh4338fZHstaEVQPt7D3rjIGBR .yOBjKWSpDPyCnDeMcbISn_Sa7Orh8y1Vp37So2dYVyOKvVQoeHE3WxSMrhi bi08uM6zKH5e6 Received: from [93.200.165.78] by web122203.mail.ne1.yahoo.com via HTTP; Mon, 29 Oct 2012 12:49:52 PDT X-Rocket-MIMEInfo: 001.001, QXMgc2hhdW4gaGFzIGluZGljYXRlZCwgdGhlcmUgaXMgbm8gbmVlZCBmb3Igam9pbiwgYWxzbyBhcyBKb3NodWEgc3VnZ2VzdGVkLCBpdCBpc8KgIGdvb2QgdG8gdXBncmFkZSB5b3VyIHNlcnZlci7CoCBhbHNvIGFkZCBpbmRleGVzIGZvciB5b3VyIHByZWRpY2F0ZXMgYW5kIGZvcmVpZ24ga2V5cyBhbmQgeW91IHdpbGwgZ2V0IGEgZGVzaXJlZCByZXN1bHQuCgpSZWdhcmRzCgoKCgoKX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KIEZyb206IFNoYXVuIFRob21hcyA8c3Rob21hc0BvcHRpb25zaG8BMAEBAQE- X-Mailer: YahooMailWebService/0.8.123.460 References: <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3901@MX40A.corp.emc.com> <508EDAB5.3090709@optionshouse.com> Message-ID: <1351540192.5874.YahooMailNeo@web122203.mail.ne1.yahoo.com> Date: Mon, 29 Oct 2012 12:49:52 -0700 (PDT) From: salah jubeh Reply-To: salah jubeh Subject: Re: Request for help with slow query To: "sthomas@optionshouse.com" , "Woolcock, Sean" Cc: "pgsql-performance@postgresql.org" In-Reply-To: <508EDAB5.3090709@optionshouse.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1629896970-851634658-1351540192=:5874" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201210/358 X-Sequence-Number: 48317 --1629896970-851634658-1351540192=:5874 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable As shaun has indicated, there is no need for join, also as Joshua suggested= , it is=A0 good to upgrade your server.=A0 also add indexes for your predic= ates and foreign keys and you will get a desired result.=0A=0ARegards=0A=0A= =0A=0A=0A=0A________________________________=0A From: Shaun Thomas =0ATo: "Woolcock, Sean" =0ACc: "p= gsql-performance@postgresql.org" =0ASent= : Monday, October 29, 2012 8:36 PM=0ASubject: Re: [PERFORM] Request for hel= p with slow query=0A =0AOn 10/29/2012 12:41 PM, Woolcock, Sean wrote:=0A=0A= >=A0 =A0 =A0 An example query that's running slowly for me is:=0A> =0A>=A0 = =A0 =A0 =A0 =A0 select tape.volser,=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tap= e.path,=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tape.scratched,=0A>=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 tape.size,=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 extract= (epoch from tape.last_write_date) as last_write_date,=0A>=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 extract(epoch from tape.last_access_date) as last_access_d= ate=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 from tape=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0= inner join filesystem=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 on (tape.file= system_id =3D filesystem.id)=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 order by last_w= rite_date desc=0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 limit 100=0A>=A0 =A0 =A0 =A0 = =A0 =A0 =A0 offset 100;=0A=0AIs this a representative example? From the loo= ks of this, you could entirely drop the join against the filesystems table,= because you're not using it in the SELECT or WHERE sections at all. You do= n't need that join in this example.=0A=0A> ->=A0 Seq Scan on tape=A0 (cost= =3D0.00..178550.57 rows=3D3219757 width=3D312)=0A> (actual time=3D2.824..18= 175.863 rows=3D3219757 loops=3D1)=0A> ->=A0 Hash=A0 (cost=3D3.01..3.01 rows= =3D101 width=3D4) (actual=0A> time=3D0.204..0.204 rows=3D101 loops=3D1)=0A>= ->=A0 Seq Scan on filesystem=A0 (cost=3D0.00..3.01 rows=3D101 width=3D4)= =0A> (actual time=3D0.004..0.116 rows=3D101 loops=3D1)=0A>=A0 =A0 =A0 =A0 = =A0 Total runtime: 66553.643 ms=0A=0AI think we can stop looking at this po= int. Because of the ORDER clause, it has to read the entire tape table beca= use you have no information on last_write_date it can use. Then, it has to = read the entire filesystem table because you asked it to do a join, even if= you threw away the results.=0A=0A>=A0 =A0 =A0 1. I added an index on last_= write_date with:=0A>=A0 =A0 =A0 =A0 and there was no improvement in query = time.=0A=0AI'm not sure 8.1 knows what to do with that. But I can guarantee= newer versions would do a reverse index scan on this index to find the top= 100 rows, even with the offset. You can also do this with newer versions, = since it's the most common query you run:=0A=0Acreate index tape_last_write= _date_idx on tape (last_write_date DESC);=0A=0AWhich would at least give yo= u forward read order when addressing this index.=0A=0A> 3. I ran the query = against the same data in Postgres 9.1.6 rather than 8.1.17=0A>=A0 =A0 using= the same hardware and it was about 5 times faster (nice work,=0A=0AIt woul= d be an order of magnitude faster than that if you add the index also.=0A= =0A> Unfortunately upgrading is not an option, so this is more of an=0A> an= ecdote. I would think the query could go much faster in either=0A> environm= ent with some optimization.=0A=0AYou desperately need to reconsider this. P= ostgreSQL 8.1 is no longer supported, and was last updated in late 2010. An= y bug fixes, including known corruption and security bugs, are no longer be= ing backported. Every day you run on an 8.1 install is a risk. The story is= similar with 8.2. Even 8.3 is on the way to retirement. You're *six* major= versions behind the main release.=0A=0AAt the very least, you need to upgr= ade PostgreSQL from 8.1.17 to 8.1.23. You're still on a version of PG that'= s almost 7-years old, but at least you'd have the most recent patch level.= =0A=0A=0A-- Shaun Thomas=0AOptionsHouse | 141 W. Jackson Blvd. | Suite 500 = | Chicago IL, 60604=0A312-444-8534=0Asthomas@optionshouse.com=0A=0A________= ______________________________________=0A=0ASee http://www.peak6.com/email_= disclaimer/ for terms and conditions related to this email=0A=0A=0A-- Sent = via pgsql-performance mailing list (pgsql-performance@postgresql.org)=0ATo = make changes to your subscription:=0Ahttp://www.postgresql.org/mailpref/pgs= ql-performance --1629896970-851634658-1351540192=:5874 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
As shaun has indicated, there = is no need for join, also as Joshua suggested, it is  good to upgrade = your server.  also add indexes for your predicates and foreign keys an= d you will get a desired result.

Regards


From: Shaun Thomas <sthomas@optionshouse.com&g= t;
To: "Woolcock, Sean= " <Sean.Woolcock@emc.com>
C= c: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Sent: Monday, October 29, 2012 8:36 PM
Subject: Re: [PERFORM] Request for he= lp with slow query

=0AOn 10/29/2012 12:41 PM, Woolco= ck, Sean wrote:

>      An example query that's run= ning slowly for me is:
>
>          s= elect tape.volser,
>              =   tape.path,
>              =   tape.scratched,
>            &n= bsp;   tape.size,
>            &n= bsp;   extract(epoch from tape.last_write_date) as last_write_date,>                extract(epoc= h from tape.last_access_date) as last_access_date
>    &nbs= p;         from tape
>       =       inner join filesystem
>      &nb= sp;           on (tape.filesystem_id =3D filesystem.id)
>    &nb= sp;         order by last_write_date desc
> =             limit 100
>    &= nbsp;         offset 100;

Is this a representati= ve example? From the looks of this, you could entirely drop the join agains= t the filesystems table, because you're not using it in the SELECT or WHERE= sections at all. You don't need that join in this example.

> -&g= t;  Seq Scan on tape  (cost=3D0.00..178550.57 rows=3D3219757 widt= h=3D312)
> (actual time=3D2.824..18175.863 rows=3D3219757 loops=3D1)<= br>> ->  Hash  (cost=3D3.01..3.01 rows=3D101 width=3D4) (ac= tual
> time=3D0.204..0.204 rows=3D101 loops=3D1)
> ->  = Seq Scan on filesystem  (cost=3D0.00..3.01 rows=3D101 width=3D4)
&g= t; (actual time=3D0.004..0.116 rows=3D101 loops=3D1)
>    &= nbsp;     Total runtime: 66553.643 ms

I think we can stop looking at this point. Be= cause of the ORDER clause, it has to read the entire tape table because you= have no information on last_write_date it can use. Then, it has to read th= e entire filesystem table because you asked it to do a join, even if you th= rew away the results.

>      1. I added an index o= n last_write_date with:
>        and there was n= o improvement in query time.

I'm not sure 8.1 knows what to do with = that. But I can guarantee newer versions would do a reverse index scan on t= his index to find the top 100 rows, even with the offset. You can also do t= his with newer versions, since it's the most common query you run:

c= reate index tape_last_write_date_idx on tape (last_write_date DESC);
Which would at least give you forward read order when addressing this inde= x.

> 3. I ran the query against the same data in Postgres 9.1.6 rather than 8.1.17
>    using the same hardware and = it was about 5 times faster (nice work,

It would be an order of magn= itude faster than that if you add the index also.

> Unfortunately= upgrading is not an option, so this is more of an
> anecdote. I woul= d think the query could go much faster in either
> environment with s= ome optimization.

You desperately need to reconsider this. PostgreSQ= L 8.1 is no longer supported, and was last updated in late 2010. Any bug fi= xes, including known corruption and security bugs, are no longer being back= ported. Every day you run on an 8.1 install is a risk. The story is similar= with 8.2. Even 8.3 is on the way to retirement. You're *six* major version= s behind the main release.

At the very least, you need to upgrade Po= stgreSQL from 8.1.17 to 8.1.23. You're still on a version of PG that's almo= st 7-years old, but at least you'd have the most recent patch level.


-- Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. |= Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@opti= onshouse.com

______________________________________________
<= br>See http://www.peak6.com/email_disclaimer/ for terms and conditions rela= ted to this email


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


--1629896970-851634658-1351540192=:5874--