Re: Forcing use of indexes

From: Pedro Alves <pmalves(at)think(dot)pt>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostGreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Forcing use of indexes
Date: 2003-04-02 15:02:24
Message-ID: 20030402150224.GA31664@cosmos.inesc.pt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Apr 02, 2003 at 09:52:19AM -0500, Tom Lane wrote:
> Pedro Alves <pmalves(at)think(dot)pt> writes:
> > 1. I run the same query (select blah ... order by foo limit bar) in 2
> > "virtualy" identical machines, both having postgres v7.3.2. The database is
> > the same (the amount of data is a bit diferent) and machine A has (much)
> > more shared buffers than Machine B; postgres uses indexes in B but not in
> > A. If I change the limit from 200 to 100, machine A starts using indexes.
> > In machine B, the optimizer only stops using indexes in limit 800. Why does
> > this happen? Is there any memory parameter that controles this behaviour?
>
> Have you vacuum analyzed recently on both machines?

Yep, in both machines

>
> shared_buffers doesn't affect the estimated cost of an indexscan.
> effective_cache_size does, also random_page_cost, but you didn't mention
> having touched those.
>

No, I didn't

>
> > 3. I have a composite index in columns foo and bar and an index in foo. I
> > noticed that making a query such as select * from table where foo=1 and
> > bar=2, postgres correctly uses foo_bar_idx. But if I use select * from
> > table where foo=1 and bar IN (1,2), posgtres uses foo_idx, having much more
> > inneficiency.
>
> Presently, you'd need an index on (bar,foo) to get a good plan for a
> query expressed that way.
>

Thats what I meant when I said 'I have a composite index in columns foo
and bar'. In the second query, it doesn't this index.

Thanks

--
Pedro Miguel G. Alves pmalves(at)think(dot)pt
THINK - Tecnologias de Informação www.think.pt
Tel: +351 21 412 56 56 Av. José Gomes Ferreira
Fax: +351 21 412 56 57 nº 13 1495-139 ALGÉS

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-04-02 15:04:42 Re: anyone know what the deal with 64.117.224.149 is?
Previous Message Tom Lane 2003-04-02 14:52:19 Re: Forcing use of indexes