| From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
|---|---|
| To: | Justin Funk <funkju(at)iastate(dot)edu> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Querying a Large Partitioned DB |
| Date: | 2009-04-12 10:57:37 |
| Message-ID: | 1239533857.16396.50.camel@ebony.2ndQuadrant |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Simon Riggs | 2009-04-12 11:01:06 | Re: No return from trigger function |
| Previous Message | Simon Riggs | 2009-04-12 10:52:57 | Re: Minimizing Recovery Time (wal replication) |