Re: [PERFORM] index on custom function; explain
Hi, On Mon, Oct 03, 2005 at 08:14:11AM -0700, [EMAIL PROTECTED] wrote: So my questions are: * Does anyone have any idea how I can integrate a function that lists all aliases for a given name into such a mapping query? what version are you using? TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Cheers, Yann ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] postgresql.conf runtime statistics default
Hi, after having migrated a 7.2 pg-database to 7.4 while upgrdaing from debian woody to debian sarge there are some more conf-Parameters to evaluate. We are running a small but continuously growing datawarehouse which has recently around 40 million fact entries. To my question: I found the parameter stats_reset_on_server_start which is set to true by default. Why did you choose this (and not false) and what are the impacts of changeing it to false? I mean, as long as I understood it, each query or statements generates some statistic data which is used by the optimizer (or anything equal) later on. So in my oppinion, wouldn't it be better so set this parameter to false and to enable a kind of a startup reset_stats option? Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgresql.conf runtime statistics default
Hi, On Thu, Jun 09, 2005 at 02:11:22PM +0100, Richard Huxton wrote: To my question: I found the parameter stats_reset_on_server_start which is set to true by default. Why did you choose this (and not false) and what are the impacts of changeing it to false? I mean, as long as I understood it, each query or statements generates some statistic data which is used by the optimizer (or anything equal) later on. So in my oppinion, wouldn't it be better so set this parameter to false and to enable a kind of a startup reset_stats option? This is administrator statistics (e.g. number of disk blocks read from this index) not planner statistics. You're right - it would be foolish to throw away planner stats. So what is best to set this parameter to and when? As I read this parameter is documented within the section 16.4.7.2. Query and Index Statistics Collector so I guess it is better to set it to false as described above. Or am I wrong? Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM]
Hi, On Fri, Jan 21, 2005 at 09:50:46AM +0100, Matt Casters wrote: Some people have been doing it using a union view. There isn't actually a partition feature. Actually, there is. If found this example on pgsql-performance: CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC ); ANALYZE super_foo ; CREATE TABLE sub_foo1 () INHERITS ( super_foo ); [...] CREATE TABLE sub_foo2 () INHERITS ( super_foo ); [...] Yes, this could be used instead of a view. But there is one thing missing. You can't just insert into super_foo and aquire the correct partition. You will still have to insert into the correct underlying table. Real partitioning will take care of correct partition selection. Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM]
Hi, On Fri, Jan 21, 2005 at 03:37:20PM +0100, Matteo Beccati wrote: CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC ); ANALYZE super_foo ; CREATE TABLE sub_foo1 () INHERITS ( super_foo ); CREATE TABLE sub_foo2 () INHERITS ( super_foo ); Yes, this could be used instead of a view. But there is one thing missing. You can't just insert into super_foo and aquire the correct partition. You will still have to insert into the correct underlying table. Real partitioning will take care of correct partition selection. I've recently used this method for partitioning data. In my setup inserts are done inside a pl/pgsql function called at regular intervals, so this isn't a problem for me. I didn't test it, but I think some rules (or a trigger) could do the trick. Yes, a pl/pgsql function or any software solution can solve this problem, but what you normally expect from a partitioning support is that you don't have to care about where to put your data due to the db will take care for that. Of cause a trigger could do this as well, but don't forget, that a trigger in dwh environments, where you process thousands of row at once during data loading, is very expensive and therefore no solution for production use. Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Hi, On Mon, Jan 10, 2005 at 11:07:55AM -0500, Alex Turner wrote: Neither Oracle nor MS-SQL have the range of stored procedure langauges that Postgresql supports. That is not true. Oracle uses PL/SQL for its stored procedures and M$-SQL does have a stored procedural language. Regards, Yann - OCA ;-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] query rewrite using materialized views
Hi, On Tue, Jan 04, 2005 at 10:06:18AM -0800, Josh Berkus wrote: are there any plans for rewriting queries to preexisting materialized views? I mean, rewrite a query (within the optimizer) to use a materialized view instead of the originating table? Automatically, and by default, no. Using the RULES system? Yes, you can already do this and the folks on the MattView project on pgFoundry are working to make it easier. I was just wondering if this might be on schedule for 8.x due to I read the thread about materialized views some days ago. If materialized views are someday implemented one should kepp this requested feature in mind due to I know from Oracle to let it improve query execution plans... Regards, Yann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] query rewrite using materialized views
Hi, are there any plans for rewriting queries to preexisting materialized views? I mean, rewrite a query (within the optimizer) to use a materialized view instead of the originating table? Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why so much time difference with a same query/plan?
Hi, On Wed, Dec 22, 2004 at 01:52:40PM -0800, Litao Wu wrote: Does the order of columns in the index matter since more than 50% customer_id = 158? I think it does not in Oracle. Will the performance be better if I change index xxx_idx to (domain, customer_id, created)? Well, in Oracle this would of cause matter. Oracle calculates index usage by being able to fill all index's attributes from the left to the right. If any one attribute within is missing Oracle would not test if it is only one attribute missing, or if all other attributes are missing within the query's where clause. Normaly you'd create an index using the most frequently parametrized attributes first, then the second ones and so on. If the usage isn't that different, you would use the most granule attribute in foremost followed by the second and so on. Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
