pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Constraint on multicolumn index
3+ messages / 2 participants
[nested] [flat]

* Constraint on multicolumn index
@ 2006-11-10 10:21 Stuart Brooks <stuartb@cat.co.za>
  2006-11-10 15:07 ` Re: Constraint on multicolumn index Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Stuart Brooks @ 2006-11-10 10:21 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I am not sure this can be done but I'm trying to constrain a sorted set
efficiently using a multicolumn index in postgres. The (simplified)
scenario is this:

CREATE TABLE T
(
  a INT,
  b INT,
  c INT
);

CREATE INDEX t_idx ON T(a,b,c);

Now I can sort using t_idx:

 select * from T order by a,b,c;  -- all good, seq scan using t_idx


I can constrain on a single variable fine:

 select * from T where (a=10 AND b=100 AND c>1000) order by a,b,c;
 -- does seq scan on t_idx and uses the index in the constraint as
expected


But if I want the next item following t=(a=10,b=100,c=1000):

 select * from T
 where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10)
 order by a,b,c;

then it just does an ordinary filter, and basically does a sequence scan
with no intelligence which isn't great if you've got a table of 20
million items.

Is there any way short of issuing 3 queries and joining them that I can
do this? I had hoped to be able to compare (a,b,c)>(10,100,1000) but of
course that evaluates to (a>10) and (b>100) and (c>1000). It feels like
there should be a simple solution to this... please help :)

Thanks
 Stuart





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

* Re: Constraint on multicolumn index
  2006-11-10 10:21 Constraint on multicolumn index Stuart Brooks <stuartb@cat.co.za>
@ 2006-11-10 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-11-14 12:50   ` Re: Constraint on multicolumn index Stuart Brooks <stuartb@cat.co.za>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2006-11-10 15:07 UTC (permalink / raw)
  To: Stuart Brooks <stuartb@cat.co.za>; +Cc: pgsql-sql

"Stuart Brooks" <stuartb@cat.co.za> writes:
> But if I want the next item following t=(a=10,b=100,c=1000):

>  select * from T
>  where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10)
>  order by a,b,c;

The correct way to handle this is to use a SQL-spec row comparison:

	where (a,b,c) > (10,100,1000)

Unfortunately, that syntax does not work per-spec in any existing
Postgres release.  It will work properly (and use the index) in
PG 8.2, for what that's worth.

Not sure if there's any reasonable workaround in PG <= 8.1.
You might want to check this old thread:
http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
(note that none of the first few responses got the point :-()  Also
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php

			regards, tom lane



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

* Re: Constraint on multicolumn index
  2006-11-10 10:21 Constraint on multicolumn index Stuart Brooks <stuartb@cat.co.za>
  2006-11-10 15:07 ` Re: Constraint on multicolumn index Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-11-14 12:50   ` Stuart Brooks <stuartb@cat.co.za>
  0 siblings, 0 replies; 3+ messages in thread

From: Stuart Brooks @ 2006-11-14 12:50 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>

> > But if I want the next item following t=(a=10,b=100,c=1000):
>
> >  select * from T
> >  where (a=10 AND b=100 AND c>1000) OR (a=10 AND b>100) OR (a>10)
> >  order by a,b,c;
>
> The correct way to handle this is to use a SQL-spec row comparison:
>
> where (a,b,c) > (10,100,1000)
>
> Unfortunately, that syntax does not work per-spec in any existing
> Postgres release.  It will work properly (and use the index) in
> PG 8.2, for what that's worth.
>
> Not sure if there's any reasonable workaround in PG <= 8.1.
> You might want to check this old thread:
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
> (note that none of the first few responses got the point :-()  Also
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00209.php

Thanks for the response. PG 8.2 looks like a good option when it is
finalized (I see it is beta 3 so shouldn't be too long should it?), but
I also need to have a closer look at the row constructor - this is a new
one for me as I have been using MySQL up til now.

The best solution I could come up with for my problem was to do a
union - something like:

(SELECT * from T WHERE (a=10 AND b=100 AND c>1000) ORDER BY a,b,c LIMIT
10)
UNION
(SELECT * from T WHERE (a=10 AND b>100) ORDER BY a,b,c LIMIT 10)
UNION
(SELECT * from T WHERE (a>10) ORDER BY a,b,c LIMIT 10)
ORDER BY a,b,c LIMIT 10;

which would use an index for each of the selects and then have to merge,
sort and limit the results. This seemed to work although it gets clumsy
if there are a whole lot of extra criteria.

Thanks again for the help,
 Stuart




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

end of thread, other threads:[~2006-11-14 12:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-10 10:21 Constraint on multicolumn index Stuart Brooks <stuartb@cat.co.za>
2006-11-10 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-14 12:50   ` Stuart Brooks <stuartb@cat.co.za>

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