I am looking at ways to speed up queries, the most common way by for
queries to be constrianed is by date range. I have indexed the date
column. Queries are still slower than i would like.
Would there be any performance increase for these types of queries if
the tables were split by month as described by Shridhar (method1) so
only the required tables were loaded from disk?
Will there be any performance increase if table partitioning is
implemented?
If method1 is likely to yield a performance increase, will there be a
large hit for other types
of queries which require all the data?
I'd be happy to post the EXPLAIN ANALYZE output for a typical query if
more info is needed.
The date column is of type timestamp (and so goes right down to seconds)
, most user queries are only concerned about whole days without the
times, (but hte time data is required for other queries) can i do
something with an index of the timestamps cast to dates? and then cast
the queries to dates too?
Thanks!
Ron Johnson Wrote:
> On Sun, 2003-08-03 at 06:31, Shridhar Daithankar wrote:
> > On 1 Aug 2003 at 13:46, Roger Hand wrote:
> [snip]
> > Here is what you can do.
> >
> > * Create a empty base table.
> > * Create a trigger the directs the select on timestamps accordingly
> > * Write a schedule to create a new table at the start of every
month and update
> > the trigger accordingly
> > * Create and maintain rules to insert into base table depending
upon the
> > timestamp.
> >
> > This is just a rough idea.
> >
> > There might be fair amount of work to get this working but surely
it is not
> > imposible.
>
> And you get a big performance hit when all those records are moved.
>
> Partitioning "should" be put on the TODO list soon after tablespaces
> (or DBA-defined directories) is implemented.
>
--
