On Wed, Jan 09, 2008 at 12:24:37PM -0500, Josh Harrison wrote:
> 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.
Ah, I didn't know that. If your description is right, there would seem to
be some pretty severe I/O penalties when using an index that doesn't cover
all the fields you want. I mean, I can see the gain, but it would seem
that's a significant cost. Anyway, no point in speculating about the
implementation details of a system I don't know in detail.
> 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
I see. Without rather more information, we're not going to be able to help
you. What you could do is take some detailed examples to the -performance
list. Oracle is terrifically capable, but Postgres can usually hold its own
when correctly tuned (there are some cases where it can't, though).
A