Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query planner problem

Query planner problem

From
"Crash Dummy"
Date:
Hi,

I've moved a database onto a much faster machine but for some reason the
query planner is opting to use seq scan methods rather than the indexes.

This is a very large database without the indexes the performance is dire -
the new machine is a 2Ghz P4 with 1GB RAM, the old machine is a 900Mhz P3
with 512Mb Ram.

Any idea why this is happening adn what I can do to solve the problem ? I've
tried dropping & rebuilding the indexes, vacuum analyze etc but to no
effect.

Cheers,

Bob

Re: Query planner problem

From
Stephan Szabo
Date:
On Sat, 9 Mar 2002, Crash Dummy wrote:

> Hi,
>
> I've moved a database onto a much faster machine but for some reason the
> query planner is opting to use seq scan methods rather than the indexes.
>
> This is a very large database without the indexes the performance is dire -
> the new machine is a 2Ghz P4 with 1GB RAM, the old machine is a 900Mhz P3
> with 512Mb Ram.
>
> Any idea why this is happening adn what I can do to solve the problem ? I've
> tried dropping & rebuilding the indexes, vacuum analyze etc but to no
> effect.

What do the schema and queries look like, and what does explain (explain
analyze if you've got 7.2) show for them?

Re: Query planner problem

From
Doug McNaught
Date:
"Crash Dummy" <crash.dummy@ntlworld.com> writes:

> Hi,
>
> I've moved a database onto a much faster machine but for some reason the
> query planner is opting to use seq scan methods rather than the indexes.
>
> This is a very large database without the indexes the performance is dire -
> the new machine is a 2Ghz P4 with 1GB RAM, the old machine is a 900Mhz P3
> with 512Mb Ram.
>
> Any idea why this is happening adn what I can do to solve the problem ? I've
> tried dropping & rebuilding the indexes, vacuum analyze etc but to no
> effect.

Post your schema and EXPLAIN output for the problem queries.

-Doug
--
Doug McNaught       Wireboard Industries      http://www.wireboard.com/

      Custom software development, systems and network consulting.
      Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

Re: Query planner problem

From
Bruce Momjian
Date:
Crash Dummy wrote:
> Hi,
>
> I've moved a database onto a much faster machine but for some reason the
> query planner is opting to use seq scan methods rather than the indexes.
>
> This is a very large database without the indexes the performance is dire -
> the new machine is a 2Ghz P4 with 1GB RAM, the old machine is a 900Mhz P3
> with 512Mb Ram.
>
> Any idea why this is happening adn what I can do to solve the problem ? I've
> tried dropping & rebuilding the indexes, vacuum analyze etc but to no
> effect.

Read the current FAQ item 4.8 on the web site.  It is more current and
should answer your question.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026