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

Search Discussions

  • Tom Lane at Oct 6, 2005 at 4:20 pm

    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
  • Han Holl at Oct 6, 2005 at 5:46 pm

    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
  • Ian Harding at Oct 7, 2005 at 4:40 pm

    On 10/6/05, 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
  • Gaetano Mendola at Oct 10, 2005 at 4:59 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 6, '05 at 2:56p
activeOct 10, '05 at 4:59p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase