[EMAIL PROTECTED] wrote:
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
month and update* Write a schedule to create a new table at the start of every
upon thethe trigger accordingly
* Create and maintain rules to insert into base table depending
it is nottimestamp.
This is just a rough idea.
There might be fair amount of work to get this working but surely
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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
