On Jan 9, 2008 12:11 PM, Andrew Sullivan <
ajs@crankycanuck.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