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
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 |