From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cristian Prieto <cristian@clickdiario.com>
Cc: 'Sean Davis' <sdavis2@mail.nih.gov>
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Index use in BETWEEN statement...
Date: Mon, 26 Sep 2005 15:17:23 -0400
Message-ID: <1896.1127762243@sss.pgh.pa.us> (raw)
In-Reply-To: <20050926175615.BA5C21015A@mail.clickdiario.com>
References: <20050926175615.BA5C21015A@mail.clickdiario.com>
"Cristian Prieto" <cristian@clickdiario.com> writes:
> mydb=# explain analyze select locid from geoip_block where
> '216.230.158.50'::inet between start_block and end_block;
> As you see it still using a sequential scan in the table and ignores the
> index, any other suggestion?
That two-column index is entirely useless for this query; in fact btree
indexes of any sort are pretty useless. You really need some sort of
multidimensional index type like rtree or gist. There was discussion
just a week or three ago of how to optimize searches for intervals
overlapping a specified point, which is identical to your problem.
Can't remember if the question was about timestamp intervals or plain
intervals, but try checking the list archives.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cristian@clickdiario.com, sdavis2@mail.nih.gov, pgsql-general@postgresql.org
Subject: Re: Index use in BETWEEN statement...
In-Reply-To: <1896.1127762243@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox