I need suggestions because I am not good enough.

I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user and now there are more than 3 million of rows.

I have a script that runs the following query often in order to see if two users are nearby:

    UL.id                          AS id, 
    UL.user_id                     AS user_id, 
    ST_X(UL.location::geometry)    AS lat, 
    ST_Y(UL.location::geometry)    AS lng, 
    UL.datetime                    AS datetime
    public.user_location AS UL
    UL.user_id <> 1234567890 AND 
    '1890-10-31 03:00:00 +00:00' - UL.datetime <= interval '1' minute AND
    '1890-10-31 03:00:00 +00:00' >= UL.datetime AND
    ST_DWithin(UL.location, ST_GeogFromText('POINT(54 -1)'), 5000)
    UL.datetime DESC;

The problem seems to be the radius, the execution time of the query grows exponentially by increasing the radius because it needs to check among more rows.

I need of a scalable solution where by increasing the radius around a given location, the execution time is almost the same. I need to "cut horizontally" the data by using before the datetime and just after the radius in the query, how can I do?

I have also the output of the EXPLAIN ANALYZE which is:

"Sort  (cost=389.72..389.73 rows=3 width=52) (actual time=136848.985..136848.985 rows=0 loops=1)"
"  Sort Key: datetime"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Bitmap Heap Scan on user_location ul  (cost=11.00..389.70 rows=3 width=52) (actual time=136848.976..136848.976 rows=0 loops=1)"
"        Recheck Cond: (location && '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography)"
"        Filter: ((user_id <> 1234567890) AND ('1890-10-31 03:00:00 +00:00'::timestamp with time zone >= datetime) AND (('1890-10-31 03:00:00 +00:00'::timestamp with time zone - datetime) <= '00:01:00'::interval minute) AND ('0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography && _st_expand(location, 5000::double precision)) AND _st_dwithin(location, '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography, 5000::double precision, true))"
"        ->  Bitmap Index Scan on users_locations_gix  (cost=0.00..11.00 rows=91 width=0) (actual time=4463.249..4463.249 rows=165622 loops=1)"
"              Index Cond: (location && '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography)"
"Total runtime: 136849.591 ms"

Thanks in advance! Cheers

  • First, you can improve the datetime comparisons (the first part is not sargable.) I suppose you don't have 3M rows, all in the same 1 minute timeframe. Then, if you do have many rows in an interval (like if you are searching in large intervals) and big radius, it's an interesting (and tough) problem. – ypercubeᵀᴹ Nov 1 '13 at 19:43
  • Thanks @ypercube! Do you mean that '1890-10-31 03:00:00 +00:00' - UL.datetime <= interval '1' minute is not sargable? Exactly, but it seems that by increasing the radius the comparisons increase at most up to the size of the table user_location. It's like if the datetime comparisons don't matter at all. – angelst00ne Nov 1 '13 at 19:45
  • Yes, make that '1890-10-31 03:00:00 +00:00' - interval '1' minute <= UL.datetime Then an index on (datetime) can be used to restrict the rows examined to only those that match both the datetime conditions, so only the 1 minute interval. – ypercubeᵀᴹ Nov 1 '13 at 19:47
  • Thanks! I made it sargable, and I'm creating an index on (datetime DESC). @ypercube Do you think that is enough to have just 1 minute interval? – angelst00ne Nov 1 '13 at 19:53
  • Well, I don't understand what you mean with "enough". What are your requirements? Search 1 minute, 3 hours, 7 years, the whole table? – ypercubeᵀᴹ Nov 1 '13 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.