Re: quick question abt pg_dump and restore

On Jan 9, 2008 12:11 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:

> On Wed, Jan 09, 2008 at 11:51:16AM -0500, Josh Harrison wrote:
>
> > accessed frequently. So clustering the table according to one index will
> > yield poor performance to queries involving other indexes.
>
> Maybe not poor, but certainly not optimised.
>
> > Index-only scan is a good solution for this I guess for queries
> involving
> > indexed columns (like in oracle) !!!
>
> I think I don't know what you mean by "index-only scan". Oracle can't be
> returning you data just by scanning an index, can it? It needs to get the
> tuple if you need it back.

For example if I have a query like
select column2 from ABC where column1 > 20
and table ABC is indexed on (column1,column2) then Oracle will not goto the
heap to fetch the tuples. It will return them from the index itself since
the column information is available in the index. But postgres always goes
to the heap even if it has all the queried columns in the index.
For queries that involves all the columns(like select * from ABC where
column1>20) ... of course oracle goes to the heap to fetch them

>
> My bet is that you haven't tuned your vacuums correctly, or you aren't
> doing
> ANALYSE often enough on the affected tables, or you need to SET STATISTICS
> higher on some of the columns in order to get better estimates (and
> therefore better plans).

I had vacuumed and analysed the tables ...tried increasing the statistics
too. But the performance compared to oracle for these types of queries(that
i had mentioned above) is pretty slow

josh