Re: Help w/speeding up range queries?

От: Tom Lane
Тема: Re: Help w/speeding up range queries?
Дата: ,
Msg-id: 12189.1162355349@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Help w/speeding up range queries?  (John Major)
Ответы: Re: Help w/speeding up range queries?  ("Luke Lonergan")
Re: Help w/speeding up range queries?  (Jim Nasby)
Список: pgsql-performance

Скрыть дерево обсуждения

Help w/speeding up range queries?  (John Major, )
 Re: Help w/speeding up range queries?  ("Luke Lonergan", )
 Re: Help w/speeding up range queries?  (Weslee Bilodeau, )
  Re: Help w/speeding up range queries?  ("Luke Lonergan", )
 Re: Help w/speeding up range queries?  (Tom Lane, )
  Re: Help w/speeding up range queries?  ("Luke Lonergan", )
   Re: Help w/speeding up range queries?  (Tom Lane, )
  Re: Help w/speeding up range queries?  (Jim Nasby, )
 Re: Help w/speeding up range queries?  ("Marcin Mank", )
 Re: Help w/speeding up range queries?  ("Simon Riggs", )

John Major <> writes:
> My problem is, I often need to execute searches of tables like these
> which find "All features within a range".
> Ie:  select FeatureID from SIMPLE_TABLE where FeatureChromosomeName like
> 'chrX' and StartPosition > 1000500 and EndPosition < 2000000;

A standard btree index is just going to suck for these types of queries;
you need something that's actually designed for spatial range queries.
You might look at the contrib/seg module --- if you can store your
ranges as "seg" datatype then the seg overlap operator expresses what
you need to do, and searches on an overlap operator can be handled well
by a GIST index.

Also, there's the PostGIS stuff, though it might be overkill for what
you want.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Heikki Linnakangas
Дата:
Сообщение: Re: big transaction slows down over time - but disk seems
От: Ben
Дата:
Сообщение: Re: big transaction slows down over time - but disk seems almost unused