Re: Query composite index range in an efficient way

From: Matthew Wakeling
Subject: Re: Query composite index range in an efficient way
Date: ,
Msg-id: alpine.DEB.2.00.0902171252000.23335@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Query composite index range in an efficient way  (Havasvölgyi Ottó)
List: pgsql-performance

Tree view

Query composite index range in an efficient way  (Havasvölgyi Ottó, )
 Re: Query composite index range in an efficient way  (Matthew Wakeling, )
 Re: Query composite index range in an efficient way  (Gregory Stark, )
  Re: Query composite index range in an efficient way  (Tom Lane, )
 Re: Query composite index range in an efficient way  ("Kevin Grittner", )
  Re: Query composite index range in an efficient way  (Havasvölgyi Ottó, )

On Tue, 17 Feb 2009, Havasvölgyi Ottó wrote:
> I created a big enough table (131072 records, and it had also a 3rd
> field with about 120 character text data). But Postgres performs a
> SeqScan.

Firstly, you should always post EXPLAIN ANALYSE results when asking about
a planning problem.

Secondly, you can't "get" Postgres to choose a particular plan (without
disruptive fiddling with the planner). Postgres will try to choose the
plan that answers the query fastest, and this may be a sequential scan.

What happens if you use the following WHERE clause?

WHERE id1 > 12 AND id1 < 56

Does Postgres use a sequential scan then?

How many rows does your query return? If it's more than about 10% of the
total rows in the table, then a sequential scan is probably the fastest
method.

Matthew

--
 Matthew: That's one of things about Cambridge - all the roads keep changing
           names as you walk along them, like Hills Road in particular.
 Sagar:   Yes, Sidney Street is a bit like that too.
 Matthew: Sidney Street *is* Hills Road.


pgsql-performance by date:

From: Havasvölgyi Ottó
Date:
Subject: Re: Query composite index range in an efficient way
From: Robert Haas
Date:
Subject: Re: Call of function inside trigger much slower than explicit function call