Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-general-owner+M193489=pgsql-general-arkari@postgresql.org>)
	id 1TmNqZ-0006O6-Dc for pgsql-general@arkaria.postgresql.org;
	Sat, 22 Dec 2012 12:06:31 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-general-owner+M193489=pgsql-general-arkari@postgresql.org>)
	id 1TmNqY-0004rj-CG for pgsql-general@arkaria.postgresql.org;
	Sat, 22 Dec 2012 12:06:30 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <haramrae@gmail.com>)
	id 1TmNqW-0004rZ-Ls; Sat, 22 Dec 2012 12:06:29 +0000
Received: from mail-ee0-f45.google.com ([74.125.83.45])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <haramrae@gmail.com>)
	id 1TmNqU-0004t8-Es; Sat, 22 Dec 2012 12:06:27 +0000
Received: by mail-ee0-f45.google.com with SMTP id d49so2938414eek.32
	for <multiple recipients>; Sat, 22 Dec 2012 04:06:25 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=x-received:subject:mime-version:content-type:from:in-reply-to:date
	:cc:content-transfer-encoding:message-id:references:to:x-mailer;
	bh=7K3GVX/BnjeH9uMEklv1ZYf/2BbexrpakPxtiQcV54M=;
	b=MAdF1UPYEIHtARAKU0JcGM9EKqDzxCcMUx5UggYgvDBuRYQr8WyTjFqBFXDDLUSR0C
	W0qCnW41dxDDoVvfmMz5qjLM//8ldiS4NUaizvVkz3PbsY+9QEjoW2ueIj+Rcl6mFhk+
	gkyh+eR/UA70InV0EUcH1zvxhItwXc8uJhwPJXvwrDFgkOa7WAiPG4J2f4K0/dSYid9M
	GPzgkCm+ti83yeJ6QKiME6YtTUw6MXyQmwMm99y+udrZwJXRK4yXklGPrQl8chK7Uw2U
	n2pIWmGl6DgXb+wF9fUZdaUtzd3kwDkwxgIwE8sCT0z10g3VddNoTmSnLLtVBDKY//ru
	GR0w==
X-Received: by 10.14.203.2 with SMTP id e2mr40010366eeo.20.1356177984708;
	Sat, 22 Dec 2012 04:06:24 -0800 (PST)
Received: from hollewijn.internal (2ee3ec6d.ftth.concepts.nl.
 [46.227.236.109])
	by mx.google.com with ESMTPS id 43sm27828281eed.10.2012.12.22.04.06.22
	(version=TLSv1/SSLv3 cipher=OTHER);
	Sat, 22 Dec 2012 04:06:22 -0800 (PST)
Subject: Re: Simple Query Very Slow
Mime-Version: 1.0 (Apple Message framework v1085)
Content-Type: text/plain; charset=us-ascii
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: 
 <CA+ixmX37YK+RZcLab_xXKywojFG8sXJFpHD3GWGZqNoo8yRrVA@mail.gmail.com>
Date: Sat, 22 Dec 2012 13:06:21 +0100
Cc: pgsql-general@postgresql.org,
 pgsql-admin@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <79F38E90-60F7-45AC-A3A0-E1EAC42515D6@gmail.com>
References: 
 <CA+ixmX37YK+RZcLab_xXKywojFG8sXJFpHD3GWGZqNoo8yRrVA@mail.gmail.com>
To: Jose Martinez <jmartinez@opencrowd.com>
X-Mailer: Apple Mail (2.1085)
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

> and here's my query
>=20
> select * from ticket=20
> where created between '2012-12-19 00:00:00' and  '2012-12-20 00:00:00'
>=20
> This was working fine until the number of records started to grow (about =
5 million) and now it's taking forever to return.=20
>=20
> Explain analyze reveals this:
>=20
> "Index Scan using ticket_1_idx on ticket  (cost=3D0.00..10202.64 rows=3D5=
2543 width=3D1297) (actual time=3D0.109..125.704 rows=3D53340 loops=3D1)"
> "  Index Cond: ((created >=3D '2012-12-19 00:00:00+00'::timestamp with ti=
me zone) AND (created <=3D '2012-12-20 00:00:00+00'::timestamp with time zo=
ne))"
> "Total runtime: 175.853 ms"

> Nothing works. What am I doing wrong? why is it selecting sequential scan=
? the indexes are supposed to make the query fast. Anything that can be don=
e to optimize it?

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

Perhaps your index got bloated? Do you have any long-running transactions s=
till active that prevent cleaning up deprecated index entries? If that's th=
e case, close the application that keeps that connection open and run a vac=
uum.
Are you vacuuming that table often enough?
If none of that helps, perhaps a REINDEX does.

Is that a dedicated database machine or is it also doing other stuff that's=
 eating up resources?

You didn't mention what version of Postgres you're on or what OS you're usi=
ng.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.



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