Join / Subquery on 2 Tables - ERROR: Can not prepare request: null
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.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at java.util.
at java.util.
at java.lang.
Caused by: java.lang.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at org.postgresql.
at org.postgresql.
... 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: