Re: [PERFORM] Index problem

2003-09-25 Thread Rigmor Ukuhe
What causes this behaviour? is there any workaround? Suggestions? How many rows are there in the table, and can you post the 'explain analyze' for both queries after doing a 'vacuum verbose analyze [tablename]'? There are about 2500 rows in that table. 1st query explain analyze: Seq Scan

Re: [PERFORM] Index problem

2003-09-25 Thread Matt Clark
There are about 2500 rows in that table. 1st query explain analyze: Seq Scan on PRIORITY_STATISTICS (cost=0.00..491.44 rows=127 width=12) (actual time=98.58..98.58 rows=0 loops=1) Total runtime: 98.74 msec 2nd query explain analyze: NOTICE: QUERY PLAN: Index Scan using

Re: [PERFORM] performance hit when joining with a view?

2003-09-25 Thread Tom Lane
Palle Girgensohn [EMAIL PROTECTED] writes: Vydefinition: SELECT p.userid, p.giver, p.first_name, p.last_name, p.email, p.default_language, p.created, p.created_by, w.course_id FROM (person p LEFT JOIN wiol w ON ((p.userid = w.userid))); explain analyze select p.pim_id, p.recipient,

Re: [PERFORM] upping checkpoints on production server

2003-09-25 Thread Robert Treat
On Wed, 2003-09-24 at 17:57, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: In .conf file I have default checkpoints set to 3, but I noticed that in my pg_xlog directory I always seem to have at least 8 log files. Since this is more than the suggested 7, I'm wondering if this means

[PERFORM] TPC-R benchmarks

2003-09-25 Thread Oleg Lebedev
Title: Message I am running TPC-R benchmarks with a scale factor of 1, which correspond to approximately 1 GB database size on PostgreSQL 7.3.4 installed on CygWin on Windows XP. I dedicated 128 MB of shared memory to my postrges installation. Most of the queries were able to complete in a

Re: [PERFORM] TPC-R benchmarks

2003-09-25 Thread Jenny Zhang
I am running TPC-H with scale factor of 1 on RedHat7.2 with the kernel 2.5.74. Q17 can always finish in about 7 seconds on my system. The execution plan is: Aggregate (cost=780402.43..780402.43

Re: [PERFORM] TPC-R benchmarks

2003-09-25 Thread Jenny Zhang
The index is created by: create index i_l_partkey on lineitem (l_partkey); I do not have any foreign key defined. Does the spec require foreign keys? When you create a foreign key reference, does PG create an index automatically? Can you try with the index? Jenny On Thu, 2003-09-25 at 14:39,

[PERFORM] Indices arent being used

2003-09-25 Thread rantunes
Hi guys Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a small problem which I hope could be resolved here. I'm trying to speed up this query: select count(*) from actvars, prodlevel

Re: [PERFORM] Indices arent being used

2003-09-25 Thread Rod Taylor
Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a small problem which I hope could be resolved here. I'm trying to speed up this query: select count(*) from actvars, prodlevel

Re: [PERFORM] TPC-R benchmarks

2003-09-25 Thread Josh Berkus
Jenny, create index i_l_partkey on lineitem (l_partkey); I do not have any foreign key defined. Does the spec require foreign keys? When you create a foreign key reference, does PG create an index automatically? No. A index is not required to enforce a foriegn key, and is sometimes not

Re: [PERFORM] TPC-R benchmarks

2003-09-25 Thread Tom Lane
Oleg Lebedev [EMAIL PROTECTED] writes: Seems like in your case postgres uses an i_l_partkey index on lineitem table. I have a foreign key constraint defined between the lineitem and part table, but didn't create an special indexes. Here is my query plan: The planner is obviously unhappy with