Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tnr7x-00088X-6P for pgsql-general@arkaria.postgresql.org; Wed, 26 Dec 2012 13:34:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tnr7v-00051g-S4 for pgsql-general@arkaria.postgresql.org; Wed, 26 Dec 2012 13:34:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tnr7u-00051P-B9 for pgsql-general@postgresql.org; Wed, 26 Dec 2012 13:34:30 +0000 Received: from smtp152.ord.emailsrvr.com ([173.203.6.152]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tnr7r-0001qB-B2 for pgsql-general@postgresql.org; Wed, 26 Dec 2012 13:34:29 +0000 Received: from smtp28.relay.ord1a.emailsrvr.com (localhost.localdomain [127.0.0.1]) by smtp28.relay.ord1a.emailsrvr.com (SMTP Server) with ESMTP id 706F0160093; Wed, 26 Dec 2012 08:34:25 -0500 (EST) X-SMTPDoctor-Processed: csmtpprox 2.7.4 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp28.relay.ord1a.emailsrvr.com (SMTP Server) with ESMTP id 6AEED160073; Wed, 26 Dec 2012 08:34:25 -0500 (EST) X-Virus-Scanned: OK Received: by smtp28.relay.ord1a.emailsrvr.com (Authenticated sender: spike-AT-ticketevolution.com) with ESMTPSA id 5B84F160093; Wed, 26 Dec 2012 08:34:18 -0500 (EST) Subject: Re: [ADMIN] Simple Query Very Slow References: <79F38E90-60F7-45AC-A3A0-E1EAC42515D6@gmail.com> <20121222122608.GA15790@awork2.anarazel.de> From: Spike Grobstein Content-Type: multipart/alternative; boundary=Apple-Mail-E7E82B2C-F9D6-44B8-9195-3A5DF48BFE7B X-Mailer: iPhone Mail (10A523) In-Reply-To: Message-Id: <728880EB-62DF-4C94-B596-F7B0178D7266@ticketevolution.com> Date: Wed, 26 Dec 2012 08:34:12 -0500 To: "pgsql-admin@postgresql.org" , "pgsql-general@postgresql.org" Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (1.0) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org --Apple-Mail-E7E82B2C-F9D6-44B8-9195-3A5DF48BFE7B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi Jose, How much ram is in the server? Can you also post the following values from p= ostgres config: work_mem shared_buffers Frequently when those are incorrectly configured, it can lead to significant= performance issues (like 40 second queries that should be sub-second). Out o= f the box, Postgres is configured incredibly conservatively as to run on eve= n the smallest of servers.=20 Also, what operating system and version of potgres? Thanks.=20 ...spike (Sent via handheld, please pardon spelling errors) On Dec 24, 2012, at 12:10 PM, Jose Martinez wrote:= > Thanks for your responses. Sorry, I forgot to mention that the query actu= ally takes 46 seconds despite what analyze (I dont quite understand the outp= ut of explain). We did perform a vacuum last Friday and it seems to help but= not too much. We'll also try to recreate the indices.=20 >=20 > Here's the output of=20 > EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM TICKET=20 > WHERE CREATED BETWEEN '2012-12-19 00:00:00' AND '2012-12-20 00:00:00' >=20 > "Index Scan using t_created_idx on ticket (cost=3D0.00..127638.47 rows=3D= 206383 width=3D183) (actual time=3D0.065..46104.557 rows=3D212126 loops=3D1)= " > " Index Cond: ((created >=3D '2012-12-19 00:00:00+00'::timestamp with tim= e zone) AND (created <=3D '2012-12-20 00:00:00+00'::timestamp with time zone= ))" > " Buffers: shared hit=3D44141 read=3D157167" > "Total runtime: 46293.384 ms" >=20 >=20 > Thanks. >=20 >=20 > On Sat, Dec 22, 2012 at 7:26 AM, Andres Freund wr= ote: >> On 2012-12-22 13:06:21 +0100, Alban Hertroys wrote: >> > > and here's my query >> > > >> > > select * from ticket >> > > where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00= ' >> > > >> > > This was working fine until the number of records started to grow (ab= out 5 million) and now it's taking forever to return. >> > > >> > > Explain analyze reveals this: >> > > >> > > "Index Scan using ticket_1_idx on ticket (cost=3D0.00..10202.64 rows= =3D52543 width=3D1297) (actual time=3D0.109..125.704 rows=3D53340 loops=3D1)= " >> > > " Index Cond: ((created >=3D '2012-12-19 00:00:00+00'::timestamp wit= h time zone) AND (created <=3D '2012-12-20 00:00:00+00'::timestamp with time= zone))" >> > > "Total runtime: 175.853 ms" >> > >> > > Nothing works. What am I doing wrong? why is it selecting sequential s= can? the indexes are supposed to make the query fast. Anything that can be d= one to optimize it? >>=20 >> Whats the time you would need? Beause the above isn't that slow. Perhaps >> the timing youre seing from your application includes transferring the >> data over a not too fast link? >>=20 >> It would be interesting to see EXPLAIN (ANALYZE, BUFFERS) $query >>=20 >> > It is not selecting sequential scan, you're looking at an index scan. T= hat should be pretty fast, and it isn't that slow - that's still sub-second p= erformance (0.176s). >> > Is that explain from the correct table? According to the results there a= re but 53 thousand rows in it, not anywhere near 5 million. >>=20 >> Well, thats the estimate *after* applying the restriction, so that seems >> sensible. >>=20 >> Greetings, >>=20 >> Andres Freund >>=20 >> -- >> Andres Freund http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >=20 --Apple-Mail-E7E82B2C-F9D6-44B8-9195-3A5DF48BFE7B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi Jose,

How m= uch ram is in the server? Can you also post the following values from postgr= es config:

work_mem
shared_buffers
<= div>
Frequently when those are incorrectly configured, it can l= ead to significant performance issues (like 40 second queries that should be= sub-second). Out of the box, Postgres is configured incredibly conservative= ly as to run on even the smallest of servers. 

Also, what operating system and version of potgres?

Thanks. 



...spike
(Se= nt via handheld, please pardon spelling errors)

On Dec 2= 4, 2012, at 12:10 PM, Jose Martinez <jmartinez@opencrowd.com> wrote:

Thanks for  your responses= . Sorry, I forgot to mention that the query actually takes 46 seconds despit= e what analyze (I dont quite understand the output of explain). We did p= erform a vacuum last Friday and it seems to help but not too much. We'll als= o try to recreate the indices. 

Here's the output of 
EXPLAIN (ANALYZE, BUFFERS)  SELECT * FROM TICKET 
WHERE CREATED BETWEEN '2012-12-19 00:00:00' AND  '2012-12-20 00= :00:00'

"Index Scan using t_created_idx on ticket  (cost=3D= 0.00..127638.47 rows=3D206383 width=3D183) (actual time=3D0.065..46104.557 r= ows=3D212126 loops=3D1)"
"  Index Cond: ((created >=3D '20= 12-12-19 00:00:00+00'::timestamp with time zone) AND (created <=3D '2012-= 12-20 00:00:00+00'::timestamp with time zone))"
"  Buffers: shared hit=3D44141 read=3D157167"
"Total run= time: 46293.384 ms"


Thank= s.


= On Sat, Dec 22, 2012 at 7:26 AM, Andres Freund <andres@2ndquadrant.com<= /a>> wrote:
On 2012-12-22 13:06:21 +0100, Alban Hertroys wrote:
> > and here's my query
> >
> > select * from ticket
> > where created between '2012-12-19 00:00:00' and  '2012-12-20 0= 0:00:00'
> >
> > This was working fine until the number of records started to grow (= about 5 million) and now it's taking forever to return.
> >
> > Explain analyze reveals this:
> >
> > "Index Scan using ticket_1_idx on ticket  (cost=3D0.00..10202= .64 rows=3D52543 width=3D1297) (actual time=3D0.109..125.704 rows=3D53340 lo= ops=3D1)"
> > "  Index Cond: ((created >=3D '2012-12-19 00:00:00+00'::ti= mestamp with time zone) AND (created <=3D '2012-12-20 00:00:00+00'::times= tamp with time zone))"
> > "Total runtime: 175.853 ms"
>
> > Nothing works. What am I doing wrong? why is it selecting sequenti= al scan? the indexes are supposed to make the query fast. Anything that can b= e done to optimize it?

Whats the time you would need? Beause the above isn't that slow. Perha= ps
the timing youre seing from your application includes transferring the
data over a not too fast link?

It would be interesting to see EXPLAIN (ANALYZE, BUFFERS) $query

> It is not selecting sequential scan, you're looking at an index scan. T= hat should be pretty fast, and it isn't that slow - that's still sub-second p= erformance (0.176s).
> Is that explain from the correct table? According to the results there a= re but 53 thousand rows in it, not anywhere near 5 million.

Well, thats the estimate *after* applying the restriction, so that see= ms
sensible.

Greetings,

Andres Freund

--
 Andres Freund                 &= nbsp;   http:= //www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

= --Apple-Mail-E7E82B2C-F9D6-44B8-9195-3A5DF48BFE7B--