Re: Querying a Large Partitioned DB

On Fri, 2009-04-10 at 09:15 -0500, Justin Funk wrote:

> I need to be able to do full text searches on the message field, and
> of course, it needs to be reasonably fast.
>
> The table is partitioned daily and has this structure:

> My typical query looks like this:
> SELECT * FROM SystemEvents WHERE message_index_col @@
> to_tsquery('english', 'Term')  LIMIT 25 OFFSET 0;

The partitioning does nothing to improve your typical query.

Loop through the tables from first to last until you have returned 25
records. That way you won't have to wait to search every table.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support