Hi,

After upgrading to 8.0.3 I see very poor performance on several indexes.
Like this: (udps is a view on main)

palga=> explain analyze select rapnaam from udps where naamvrouw like 'vos%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using nv on main (cost=0.00..242.65 rows=60 width=14) (actual
time=6.475..11598.502 rows=5692 loops=1)
Index Cond: (((naamvrouw)::text >= 'vos'::character varying) AND
((naamvrouw)::text < 'vot'::character varying))
Filter: ((naamvrouw)::text ~~ 'vos%'::text)
Total runtime: 11606.250 ms
(4 rows)

The index was created with:
CREATE INDEX nv ON main USING btree (naamvrouw);

Database was recently analyzed. Clearly, something has to be tuned that didn't
need tuning on 7.4.3 ? (Main table has about 1.7 million records).

Any suggestions welcome.

Cheers,

Han Holl

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

han.holl@informationslogik.nl writes:
> After upgrading to 8.0.3 I see very poor performance on several indexes.
> ...
> Database was recently analyzed. Clearly, something has to be tuned that didn't
> need tuning on 7.4.3 ? (Main table has about 1.7 million records).

No, there's no reason for 8.0 to be slower at this than 7.4, if all else
is equal. I'm betting that all else is not equal. Maybe you are using
a different encoding or locale in the new installation than the old?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thursday 06 October 2005 18:20, Tom Lane wrote:
>
> No, there's no reason for 8.0 to be slower at this than 7.4, if all else
> is equal. I'm betting that all else is not equal. Maybe you are using
> a different encoding or locale in the new installation than the old?
>
Well, I suspect that something is not equal as well. The trouble is I can't
seem to find it.
We're going to replay what happened on a different machine, and hopefully will
find something.

Thanks,

Han Holl

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 10/6/05, han.holl@informationslogik.nl <han.holl@informationslogik.nl> wrote:
> On Thursday 06 October 2005 18:20, Tom Lane wrote:
> >
> > No, there's no reason for 8.0 to be slower at this than 7.4, if all else
> > is equal. I'm betting that all else is not equal. Maybe you are using
> > a different encoding or locale in the new installation than the old?
> >
> Well, I suspect that something is not equal as well. The trouble is I can't
> seem to find it.
> We're going to replay what happened on a different machine, and hopefully will
> find something.
>

When I went from 7.4 to 8.0 I had queries that were significantly
slower. I had to tweak work_mem to get them to run at reasonable
speed, and then they were faster than 7.4 with default sort_mem.
Can't remember the details of the queries right now.

> Thanks,
>
> Han Holl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

---------------------------(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

Tom Lane wrote:
> han.holl@informationslogik.nl writes:
>> After upgrading to 8.0.3 I see very poor performance on several indexes.
>> ...
>> Database was recently analyzed. Clearly, something has to be tuned that didn't
>> need tuning on 7.4.3 ? (Main table has about 1.7 million records).
>
> No, there's no reason for 8.0 to be slower at this than 7.4, if all else
> is equal. I'm betting that all else is not equal. Maybe you are using
> a different encoding or locale in the new installation than the old?

Mmm, sure 8.0 is not slower than 7.4 in certain scenarios?

Consider:

select f1(id), f2(id), ..., fn(id) from my_view;

where fi is eligible for be marked as "STABLE" but is not.

In 8.0 in that select are involved n+1 snapshots instead of one as it
in 7.4. Could this be a performance issue ?

Regards
Gaetano Mendola