From: jcfischer <jcfischer.lists@gmail.com>
To: pgsql-performance@postgresql.org
Subject: nested query on last n rows of huge table
Date: 24 Feb 2006 06:13:31 -0800
Message-ID: <1140790410.923941.261250@p10g2000cwp.googlegroups.com> (raw)
Hi list
I'm fairly new to Postgres so bear with me. Googling and searching the
list, I didn't find anything that resembled my problem.
I have a large table with ca. 10 million inserts per day (fairly simple
data: timestam, a couple of id's and a varchar message)
I run a query every couple of minutes that looks at the new entries
since the last run and retrieves them for further processing (using a
WHERE eventtime > '2006-02-24 14:00:00' ) to limit to the most recent
entries
These queries run around 40-50 seconds (largely due to some LIKE %msg%
threwn in for good measure). Postgres performs a seq table scan on
those queries :-(
My idea is to limit the search to only the last n entries because I
found that a
SELECT * from table ORDER eventtime DESC limit 1000
is very fast. Because the inserts are in chronolgical order, I can
store the sequential id of the highest row from the last query and
subtract that from the current high row count to determine that number.
Is there a way to limit the expensive query to only those last 1000 (or
whatever) results?
I have tried to nest SELECTS but my SQL-fu is to limited to get
anything through the SQL processor :-)
thanks
Jens-Christian Fischer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jcfischer.lists@gmail.com
Subject: Re: nested query on last n rows of huge table
In-Reply-To: <1140790410.923941.261250@p10g2000cwp.googlegroups.com>
References: <1140790410.923941.261250@p10g2000cwp.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox