Join / Subquery on 2 Tables - ERROR: Can not prepare request: null

Asked by Brian Allen

We have a query that takes about 8 hours using Postgres 9.2, and are testing Stado to speed it up.

I built Stado using the ~sgdg/stado/stado branch, and created a cluster of 48 nodes, all on the same physical server. The server has 64 cores and 384 GB of RAM.

I created the following tables and imported data (331,106 and 1,124,421 rows):

CREATE TABLE trails1 (
 id text,
 a_lat double precision,
 a_long double precision,
 b_lat double precision,
 b_long double precision,
 trail_id character varying(20),
 type character varying(4),
 distance numeric(10,5)
) PARTITIONING KEY distance ON ALL;

CREATE TABLE trails2 (
 a_lat double precision,
 a_long double precision,
 b_lat double precision,
 b_long double precision,
 type character varying(5),
 distance numeric(16,8)
) PARTITIONING KEY distance ON ALL;

I can do simple queries and get the expected results:
SELECT count(*) as count FROM trails1 WHERE a_lat > 42 AND a_lat < 43 AND a_long > -100 AND a_long < -90;
 count
-------
  1457
(1 row)

But when I do this query I get "ERROR: Can not prepare request: null".

SELECT
 count(*) as count
FROM
 trails1
WHERE
 not exists (
  SELECT
   'x'
  FROM
   trails2
  WHERE
   trails2.a_lat >= trails1.a_lat - 0.000833 AND
   trails2.a_lat <= trails1.a_lat + 0.000833 AND
   trails2.a_long >= trails1.a_long - 0.000833 AND
   trails2.a_long <= trails1.a_long + 0.000833 AND
   trails2.b_lat >= trails1.b_lat - 0.000833 AND
   trails2.b_lat <= trails1.b_lat + 0.000833 AND
   trails2.b_long >= trails1.b_long - 0.000833 AND
   trails2.b_long <= trails1.b_long + 0.000833 AND
   (
    trails2.type = trails1.type OR
    trails2.type = 'S'
   ) AND
   trails2.distance >= trails1.distance - 1.0 AND
   trails2.distance <= trails1.distance + 1.0
  );

This is the error:
2013-08-07 16:36:54,397 - ERROR Catching throwable:
org.postgresql.stado.exception.XDBServerException: Can not prepare request: null
 at org.postgresql.stado.engine.ExecutableRequest.setSQLObject(Unknown Source)
 at org.postgresql.stado.protocol.RequestAnalyzer.getExecutableRequest(Unknown Source)
 at org.postgresql.stado.protocol.PgProtocolSession.run(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.NullPointerException
 at org.postgresql.stado.optimizer.Optimizer.containsOnlyLookupsAndParChilds(Unknown Source)
 at org.postgresql.stado.optimizer.Optimizer.finishTree(Unknown Source)
 at org.postgresql.stado.optimizer.Optimizer.buildTrees(Unknown Source)
 at org.postgresql.stado.optimizer.Optimizer.buildAndEvaluateTrees(Unknown Source)
 at org.postgresql.stado.optimizer.Optimizer.determineQueryPath(Unknown Source)
 at org.postgresql.stado.queryproc.QueryProcessor.prepare(Unknown Source)
 at org.postgresql.stado.queryproc.QueryProcessor.prepare(Unknown Source)
 at org.postgresql.stado.parser.SqlSelect.prepare(Unknown Source)
 ... 6 more

Any suggestions?

Question information

Language:
English Edit question
Status:
Solved
For:
Stado Edit question
Assignee:
No assignee Edit question
Solved by:
Brian Allen
Solved:
Last query:
Last reply:
Revision history for this message
Xiaobo Gu (guxiaobo1982) said :
#1

I suggest you break this complex query into small ones, and execute them one by one, maybe you can got your results.

Revision history for this message
Brian Allen (3-launchpad-9) said :
#2

Thanks for that suggestion.

I was able to do the outer query, then loop over those results and use the values to do the inner query for each. Using that approach I was able to get the results in about 23 minutes (much better than 8+ hours!).

Thanks!

Revision history for this message
Brian Allen (3-launchpad-9) said :
#3

By the way, I marked this as solved (because I was able to get the results I needed in another way), but it would be great if Stado was able to handle this type of query in a parallel manner directly.

Thanks

Revision history for this message
Xiaobo Gu (guxiaobo1982) said :
#4

By the way, which version of stado do you use, and can you help me with this question https://answers.launchpad.net/stado/+question/232606

or can you share with me your stado.config file, thanks, my email address is <email address hidden>.