phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Perko, Ralph J" <>
Subject Re: indexed query question
Date Wed, 08 Apr 2015 15:48:13 GMT

Thanks for the fix.  Which versions are these available in?


Ralph Perko
Pacific Northwest National Laboratory
(509) 375-2272

From: Maryann Xue <<>>
Reply-To: "<>" <<>>
Date: Tuesday, April 7, 2015 at 10:43 AM
To: "<>" <<>>
Subject: Re: indexed query question

Hi Ralph,

Now that we have implemented (on master)
and, I think you should be able to enable
your multiple index use case, which actually looks great to us. And please let us know if
there are other problems.

For the AND logic, you still need to use multiple subqueries connected by AND, like I mentioned

SELECT * FROM t WHERE pk IN (SELECT pk from t where q1 = ?) AND pk IN (SELECT pk from t where
q2 = ?);

And for the OR logic, you can now use UNION ALL in your subquery connecting those "or" conditions.
For example:

SELECT * FROM t WHERE pk IN (SELECT pk from t where q1 = ? UNION ALL SELECT pk from t where
q2 = ?);


On Mon, Jan 19, 2015 at 2:34 PM, Maryann Xue <<>>
Hi Ralph,

I think in your case this is indeed a nice approach. Given that INTERSECT is not yet supported
in Phoenix, you can instead use AND to connect your conditions, which would work almost as
efficiently as applying INTERSECT on your inner queries:

SELECT * FROM t WHERE pk IN (SELECT pk from t where q1 = ?) AND pk IN (SELECT pk from t where
q2 = ?);

If your query is of OR logic, unfortunately there is currently no optimal way to do this until
we have UNION ( implemented. That is because
with OR subqueries, we do a left join instead of a semi join, and left joins do not use PK
skip scan according to its semantics. A query similar to the above example with OR logic would
only run slower than one simply doing a full-scan on the main data table (without using the
index table at all).


On Mon, Jan 19, 2015 at 1:25 PM, Perko, Ralph J <<>>

I have a question about the most efficient way to query many indexed columns.  Here is the

Say I have a table with 100 fields

Table {f1,f2,f3,f4,…f100}

The first 10 fields are core fields and the client wishes to query them in any combination.

This is too many fields to create a secondary index for every combination so I create just
10, one for each core field.

Then when a query is submitted, I create a single query for each secondary index and return
just the PK, thus taking advantage to the index:

Example – for each indexed field included in the query:

SELECT PK FROM Table WHERE <indexed field> = <some value>’;

Then once I have a list of all PK values from all the index queries I will either get the
combination or intersection of all PKs, depending on the query logic (and/or)and then run
a final select:


Does this sound like a reasonable approach?


View raw message