pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index type
2+ messages / 2 participants
[nested] [flat]

* Index type
@ 2004-07-20 21:46  Ilia Kantor <algolist@manual.ru>
  0 siblings, 1 reply; 2+ messages in thread

From: Ilia Kantor @ 2004-07-20 21:46 UTC (permalink / raw)
  To: pgsql-performance

Hello,

I have a request like SELECT ... WHERE x<=A<=y AND t<=B<=u AND z<=C<=w AND ..
5 columns are in BETWEEN clauses.

What is the best index I could use?

If I create btree index on all columns (A,B,C..), here is what explain
analyze gives me:
-----------------------------------------------------------------
 Index Scan using all_ind on test2  (cost=0.00..4.51 rows=1 width=24) (actual ti
me=0.000..0.000 rows=5 loops=1)
   Index Cond: ((a >= '2004-07-20 23:50:50'::timestamp without time zone) AND (a
 <= '2004-07-21 23:50:50'::timestamp without time zone) AND (b >= '2004-07-20 23
:50:50'::timestamp without time zone) AND (b <= '2004-07-21 23:50:50'::timestamp
 without time zone) AND (c >= '2004-07-20 23:50:50'::timestamp without time zone
) AND (c <= '2004-07-21 23:50:50'::timestamp without time zone))


Is such search really optimal?

I remember we used k-d trees for geometric data with independent
coords.. Is that the same as btree for multiple columns I wonder.



-- 
Best regards,
 Ilia                            mailto:algolist@manual.ru




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Index type
@ 2004-08-16 23:16  Josh Berkus <josh@agliodbs.com>
  parent: Ilia Kantor <algolist@manual.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2004-08-16 23:16 UTC (permalink / raw)
  To: Algolist <algolist@manual.ru>; +Cc: pgsql-performance

Ilia,

> If I create btree index on all columns (A,B,C..), here is what explain
> analyze gives me:
> -----------------------------------------------------------------
>  Index Scan using all_ind on test2  (cost=0.00..4.51 rows=1 width=24)
> (actual ti me=0.000..0.000 rows=5 loops=1)
>    Index Cond: ((a >= '2004-07-20 23:50:50'::timestamp without time zone)
> AND (a <= '2004-07-21 23:50:50'::timestamp without time zone) AND (b >=
> '2004-07-20 23
>
> :50:50'::timestamp without time zone) AND (b <= '2004-07-21
> : 23:50:50'::timestamp
>
>  without time zone) AND (c >= '2004-07-20 23:50:50'::timestamp without time
> zone ) AND (c <= '2004-07-21 23:50:50'::timestamp without time zone))

Looks good to me.   It's a fully indexed search, which it should be with 
BETWEEN.  The only thing you need to ask yourself is whether or not you've 
selected the columns in the most selective order (e.g. most selective column 
first).

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-08-16 23:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-20 21:46 Index type Ilia Kantor <algolist@manual.ru>
2004-08-16 23:16 ` Josh Berkus <josh@agliodbs.com>

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