Skip site navigation (1) Skip section navigation (2)

Re: Query composite index range in an efficient way

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query composite index range in an efficient way
Date: 2009-02-17 12:56:54
Message-ID: alpine.DEB.2.00.0902171252000.23335@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Gregory StarkDate: 2009-02-17 13:10:11
Subject: Re: Query composite index range in an efficient way
Previous:From: Havasvölgyi OttóDate: 2009-02-17 12:44:48
Subject: Query composite index range in an efficient way

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group