Re: [PERFORM] index / sequential scan problem

2003-07-18 Thread Tom Lane
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= [EMAIL PROTECTED] writes: On Fri, 18 Jul 2003, Fabian Kreitner wrote: Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Doesn't sound very good and it will most likely make other queries slower. Seems like a reasonable approach to

Re: [PERFORM] index / sequential scan problem

2003-07-18 Thread scott.marlowe
On Fri, 18 Jul 2003, Tom Lane wrote: =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= [EMAIL PROTECTED] writes: On Fri, 18 Jul 2003, Fabian Kreitner wrote: Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Doesn't sound very good and it will most likely make other queries

Re: [PERFORM] index / sequential scan problem

2003-07-18 Thread Dennis Björklund
On Fri, 18 Jul 2003, Tom Lane wrote: Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Doesn't sound very good and it will most likely make other queries slower. Seems like a reasonable approach to me --- certainly better than setting random_page_cost to

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 11:01, Fabian Kreitner wrote: psql (PostgreSQL) 7.2.2 perg_1097=# VACUUM ANALYZE ; VACUUM perg_1097=# EXPLAIN ANALYZEselect notiz_id, obj_id, obj_typ perg_1097-# fromnotiz_objekt a perg_1097-# where not exists perg_1097-# ( perg_1097(# select

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Paul Thomas
On 17/07/2003 10:01 Fabian Kreitner wrote: Hi Fabian, When you are doing these kinds of tests, you need to be aware that the kernel may have most of your data cached after the first query and this may be why the second query appears to run faster. Also don't be worried if the planner chooses

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Fabian Kreitner
At 12:12 17.07.2003, you wrote: On 17/07/2003 10:01 Fabian Kreitner wrote: Hi Fabian, When you are doing these kinds of tests, you need to be aware that the kernel may have most of your data cached after the first query and this may be why the second query appears to run faster. I thought of

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Fabian Kreitner
At 11:17 17.07.2003, Shridhar Daithankar wrote: On 17 Jul 2003 at 11:01, Fabian Kreitner wrote: psql (PostgreSQL) 7.2.2 perg_1097=# VACUUM ANALYZE ; VACUUM perg_1097=# EXPLAIN ANALYZEselect notiz_id, obj_id, obj_typ perg_1097-# fromnotiz_objekt a perg_1097-# where not

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 13:12, Fabian Kreitner wrote: At 11:17 17.07.2003, Shridhar Daithankar wrote: How about where ma_id = 2001::integer and ma_pid = 1097::integer in above query? I dont really understand in what way this will help the planner but ill try. That is typecasting.

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Fabian Kreitner
At 14:34 17.07.2003, you wrote: On 17/07/2003 12:13 Fabian Kreitner wrote: That is what I read too and is why Im confused that the index is indeed executing faster. Can this be a problem with the hardware and/or postgress installation? It's more likely that the OS has most of the data cached

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 14:50, Fabian Kreitner wrote: At 14:34 17.07.2003, you wrote: I expect you will find that the third query is also a lot faster that the first query. Im afraid, no. Database has been stopped / started right before this. perg_1097=# set enable_seqscan to true; SET

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Jord Tanner
I've seen similar behavior in my own queries. I found that reducing random_page_cost from the default of 4 down to 2 caused the query to choose the index, and resulted in an order of magnitude improvement on some queries. On Thu, 2003-07-17 at 05:50, Fabian Kreitner wrote: At 14:34 17.07.2003,

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Tom Lane
Paul Thomas [EMAIL PROTECTED] writes: 2) enable_seqscan = false Seq Scan on notiz_objekt a (cost=1.00..100111719.36 rows=15561 width=12) (actual time=0.25..535.75 rows=31122 loops=1) I've just noticed this. Something is not right here. Look at the crazy cost estimation for the

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Tom Lane
Fabian Kreitner [EMAIL PROTECTED] writes: That is what I read too and is why Im confused that the index is indeed executing faster. Can this be a problem with the hardware and/or postgress installation? I think the actual issue here is that you are executing the EXISTS subplan over and over,

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Fabian Kreitner
At 20:12 17.07.2003, Tom Lane wrote: Fabian Kreitner [EMAIL PROTECTED] writes: That is what I read too and is why Im confused that the index is indeed executing faster. Can this be a problem with the hardware and/or postgress installation? I think the actual issue here is that you are executing

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Fabian Kreitner
Hi all, Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Anything I need to consider when raising it to such high values? Thanks for the help, Fabian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Dennis Björklund
On Fri, 18 Jul 2003, Fabian Kreitner wrote: Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Doesn't sound very good and it will most likely make other queries slower. You could always turn off sequential scan before that query and turn it on after. Anything I need