File: ranges.sql

package info (click to toggle)
prefix 1.1.1-1
  • links: PTS
  • area: main
  • in suites: wheezy
  • size: 648 kB
  • sloc: ansic: 1,314; sql: 367; makefile: 60
file content (26 lines) | stat: -rw-r--r-- 1,177 bytes parent folder | download | duplicates (7)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
\timing

drop table if exists ranges_presort ;
create table ranges_presort as select prefix::prefix_range, name, shortname, state from prefixes ;
create index idx_prefix_presort on ranges_presort using gist(prefix gist_prefix_range_presort_ops);

drop table if exists ranges_jordan ;
create table ranges_jordan as select prefix::prefix_range, name, shortname, state from prefixes ;
create index idx_prefix_jordan on ranges_jordan using gist(prefix gist_prefix_range_jordan_ops);

drop table if exists ranges;
create table ranges as select prefix::prefix_range, name, shortname, state from prefixes ;
create index idx_prefix on ranges using gist(prefix gist_prefix_range_ops);

\echo
\echo select * from numbers n join ranges_presort r on r.prefix @> n.number;
explain analyze select * from numbers n join ranges_presort r on r.prefix @> n.number;
\echo

\echo select * from numbers n join ranges_jordan r on r.prefix @> n.number;
explain analyze select * from numbers n join ranges_jordan r on r.prefix @> n.number;
\echo

\echo select * from numbers n join ranges r on r.prefix @> n.number;
explain analyze select * from numbers n join ranges r on r.prefix @> n.number;
\echo