Databases
StackOverflow2013
Postsdbo
POIs Hadoop Suitable For This?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs Hadoop Suitable For This?
    primarykey
    data
    text
    <p>We have some Postgres queries that take 6 - 12 hours to complete and are wondering if Hadoop is suited to doing it faster. We have (2) 64 core servers with 256GB of RAM that Hadoop could use.</p> <p>We're running PostgreSQL 9.2.4. Postgres only uses one core on one server for the query, so I'm wondering if Hadoop could do it roughly 128 times faster, minus overhead.</p> <p>We have two sets of data, each with millions of rows.</p> <p>Set One:</p> <pre> id character varying(20), a_lat double precision, a_long double precision, b_lat double precision, b_long double precision, line_id character varying(20), type character varying(4), freq numeric(10,5) </pre> <p>Set Two:</p> <pre> a_lat double precision, a_long double precision, b_lat double precision, b_long double precision, type character varying(4), freq numeric(10,5) </pre> <p>We have indexes on all lat, long, type, and freq fields, using btree. Both tables have "VACUUM ANALYZE" run right before the query.</p> <p>The Postgres query is:</p> <pre><code>SELECT id FROM setone one WHERE not exists ( SELECT 'x' FROM settwo two WHERE two.a_lat &gt;= one.a_lat - 0.000278 and two.a_lat &lt;= one.a_lat + 0.000278 and two.a_long &gt;= one.a_long - 0.000278 and two.a_long &lt;= one.a_long + 0.000278 and two.b_lat &gt;= one.b_lat - 0.000278 and two.b_lat &lt;= one.b_lat + 0.000278 and two.b_long &gt;= one.b_long - 0.000278 and two.b_long &lt;= one.b_long + 0.000278 and ( two.type = one.type or two.type = 'S' ) and two.freq &gt;= one.freq - 1.0 and two.freq &lt;= one.freq + 1.0 ) ORDER BY line_id </code></pre> <p>Is that the type of thing Hadoop can do? If so can you point me in the right direction?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1517922
    1. USuser1517922
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs Hadoop Suitable For This?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs Hadoop Suitable For This?
      1. USChaos
      1. VTFavorite
    3. VO
      singulars
      1. POIs Hadoop Suitable For This?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COLots of detail missing to get a good answer. What version of postgresql? What do your tables look like? Have you ANALYZED them? Have you indexed the lat and long? Have you looked at PostGIS? Are you able to split the query into logical units that can run in parallel?
      singulars
      1. POIs Hadoop Suitable For This?
      1. USbma
    2. COAnd why haven't you just split the query into grids? It looks very geographically constrained. That's got to be less effort than porting the whole system over to hadoop.
      singulars
      1. POIs Hadoop Suitable For This?
      1. USRichard Huxton
    3. COI updated the question with more detail about the Postgres version, tables, and indexes. I'll try with PostGIS. I'm not familiar with splitting the query into logical units. Can you explain more about that?
      singulars
      1. POIs Hadoop Suitable For This?
      1. USuser1517922
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload