pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mischa Sandberg <mischa_sandberg@telus.net>
To: pgsql-performance@postgresql.org
Subject: Range query optimization
Date: Thu, 24 Jun 2004 21:24:58 GMT
Message-ID: <KEHCc.11583$HS3.659@edtnps84> (raw)

I'm trying to make a (qua-technical, qua-business) case for switching from
MS SQL, and one of the types of query that really doesn't sit well with MS
SQL2K is:

-- All fields integers or equivalent.
-- Table T(k, x: nonkey fields...)
-- Table U(k, a, z: m)    -- for each value of (k) a set of non-intersecting
ranges [a,z) that map to (m) values.

    select T.*, U.m from T join U on T.k=U.k and T.x >= U.a and T.x < U.z

Typically there are are about 1000-2000 U rows per value of (k), about 100K
values of (k) and about 50M
values of T.

By itself, this type of query grinds the CPU to dust. A clustered index on
fields of U (take your pick) barely halves the problem of the loop through
1000-2000 rows of U for each row of T.  Hash join likewise.
The current workaround is a 'manual'  radix index on top of the range table,
but it's something of a hack.

Would the geometric of extensions handle such queries efficiently? I'm not
familiar with applying R-trees to linear range problems.
----
"Dreams come true, not free." -- S.Sondheim





Message-ID: <KEHCc.11583$HS3.659@edtnps84>
Permalink:  ../KEHCc.11583$HS3.659@edtnps84/
Also on:    postgresql.org/message-id/KEHCc.11583$HS3.659@edtnps84

 · 

reply

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: mischa_sandberg@telus.net
  Subject: Re: Range query optimization
  In-Reply-To: <KEHCc.11583$HS3.659@edtnps84>

* 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