-2

Here's a simplified sample query, done the "traditional" way (sample queries tested in PostgreSQL 8.3):

PREPARE a AS SELECT *
FROM users
WHERE uid=$1
    OR parent=$2;
EXECUTE a(0,0);

The trouble is, it's cumbersome to pass the same bind variable twice (moreso for more complex queries when it's needed half a dozen times or more).

Of course, SQL allows the same bind variable to used multiple times in the same query:

PREPARE b AS SELECT *
FROM users
WHERE uid=$1
    OR parent=$1;
EXECUTE b(0);

But many DB API layers don't support this (or don't easily), thus making it even more cumbersome to use this approach.

I can get around this by adding a JOIN against a sub-select as such:

PREPARE c AS SELECT uids.*
FROM users
JOIN (SELECT $1::INT AS uid) AS x ON true
WHERE uids.uid=x.uid 
    OR uids.parent=x.uid;
EXECUTE c(0);

Explain shows that the latter option has, as one would expect, the most complex execution plan, however, at least in this example, it consistently executes faster than a, and barely slower than b (although preparing the query takes longer than either of the other two options).

So my question is:

Is this sort of JOIN/sub-select construct considered good or bad form for more complex queries, as a way to avoid repeating bind variables?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189

1 Answers1

2

Not a direct answer to your question, but in this particular case, you should be able to rewrite your query like this:

PREPARE b AS SELECT *
FROM users
WHERE $1 IN (uid, parent);

EXECUTE b(0);
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Indeed, that would work *in this case* (it wouldn't work in the more complex case I actually care about :) ), but even so it won't use indexes on the `uid` and `parent` columns, if I'm not mistaken (and if my rudimentary tests are accurate). – Jonathan Hall Jul 17 '12 at 20:54
  • @Flimzy Logically, it should be the same thing as `uid=$1 OR parent=$1`, so it should be executed in the same way. That being said, perhaps you have hit some peculiarity of ProstgreSQL query optimizer? In any case, I recommend measuring on realistic amounts of data to be sure - I'm no expert on PostgreSQL optimizer, but many DBMSes will resort to table scans on small tables even when indexes are available. – Branko Dimitrijevic Jul 17 '12 at 21:02
  • Indeed... I did my tests against a larger table. But, sadly, that wasn't the only variable that changed, so I can't be sure my test was valid. This is a good strategy to keep in my bag-o-tricks. – Jonathan Hall Jul 17 '12 at 21:07