pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex type
2+ messages / 2 participants
[nested] [flat]
* Index type
@ 2004-07-20 21:46 Ilia Kantor <algolist@manual.ru>
2004-08-16 23:16 ` Re: Index type Josh Berkus <josh@agliodbs.com>
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-07-20 21:46 Index type Ilia Kantor <algolist@manual.ru>
@ 2004-08-16 23:16 ` Josh Berkus <josh@agliodbs.com>
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