On Saturday 17 April 2004 13:23, Development - multi.art.studio wrote:
> hello everyone,
>
> Richard Huxton wrote:
> >It's not using the date index because it's using the id index - there's
> > only 10 matches, so that looks like a good choice to me. It takes less
> > than 1ms, so I'm not sure this is a good example of a problem.
>
> thanks all !
> but this doesnt help me a lot, i dont know what what to do? im not an
> expert. ok, i could use another site_id and it will grow up and gives 500
> or more rows back from the select clause. this takes a lot of more time.
> would this help?
If that is the problem, that's what you'll need to post.
> for me it looks as pg742 is not as performant as 7.1 ? and takes a lot of
> more time, and doesnt look for the correct index? but why this? why does it
> take longer than in 7.1? any ideas? what can i do?
It's not something people are generally seeing. In most cases performance is
the same or slightly better. For some queries it can be a lot better.
What I suggest:
1. Compare the two postgresql.conf files and any other config settings and
make sure you know what differences there are and why.
2. Identify what queries seem to be the cause of the problem, and pick one you
think is a good example.
3. VACCUM FULL and ANALYZE both 7.1 and 7.4 databases.
4. Stop the 7.4 server and run EXPLAIN ANALYSE (from psql) for 7.1. If you
want to make sure the data is cached, run it three times and use the last
one.
While you're running the explain, execute "vmstat 1 > vmstat.trace-7.1.txt" in
another terminal.
5. Stop the 7.1 server, restart 7.4 and run EXPLAIN ANALYSE (from psql) for
7.4, same as before, verify that the same results are returned. Same as
before for the caching.
Trace using vmstat for this too.
Post the query SQL and both EXPLAIN ANALYSE outputs along with table
definitions and row counts for each table involved and we can see exactly
where the problem is. If you think this is too much info, you can post it on
the web instead, that's fine.
If you're using the vmstat info, you could post that too, otherwise just keep
it safe in case it's needed.
--
Richard Huxton
Archonet Ltd