Merge sort/postgis performance tweaking? - Mailing list pgsql-general

SELECT
read_date,
to_char(read_date,'YYYY-MM-DD') as text_date,
CASE WHEN lat_2>lat_1 THEN
ns_dist*-1
ELSE
ns_dist
END as ns_dist,
CASE WHEN lon_2>lon_1 THEN
ew_dist*-1
ELSE
ew_dist
END as ew_dist,
CASE WHEN alt_2>alt_1 THEN
(ud_dist*-1)::float
ELSE
ud_dist::float
END as ud_dist,
lat_e/10::float as lat_e,
lon_e/10::float as lon_e,
alt_e/10::float as alt_e,
        CASE WHEN rapid THEN
            1
        ELSE
            0
        END as rapid
FROM
(SELECT
g1.read_date as read_date,
g1.rapid as rapid,
g1.lat as lat_1,
g1.lon as lon_1,
g1.alt as alt_1,
g2.lat as lat_2,
g2.lon as lon_2,
g2.alt as alt_2,
ST_Distance(ST_SetSRID( ST_Point( g1.lon, g1.lat), 4326)::geography,
ST_SetSRID( ST_Point( g1.lon, g2.lat), 4326)::geography)/1000 as ns_dist,
ST_Distance(ST_SetSRID( ST_Point( g1.lon, g1.lat), 4326)::geography,
ST_SetSRID( ST_Point( g2.lon, g1.lat), 4326)::geography)/1000 as ew_dist,
g1.alt-g2.alt ud_dist,
sqrt(g1.lat_e^2+g2.lat_e^2) as lat_e,
sqrt(g1.lon_e^2+g2.lon_e^2) as lon_e,
sqrt(g1.alt_e^2+g2.alt_e^2) as alt_e
FROM gps_data g1
INNER JOIN gps_data g2 ON g2.read_date=g1.read_date
WHERE g1.station=%s AND g2.station=%s
) g3
ORDER BY read_date;

Which, when executed on my system for a particular par of g1.station and g2.station takes about 190ms to run (explain output here: https://explain.depesz.com/s/8Nf9 ). This is not horrible, but when running this query for a dozen stations, it becomes noticeable. As such, I was wondering if I could do better. Looking at that explain output, I noticed that the largest time sink was the Merge Join at 114ms. Some research into that gave me the command "set enable_mergejoin=off” which I tried just for fun to see what would happen. 

Somewhat to my surprise, this actually shaved a good 80ms off the total run time (see https://explain.depesz.com/s/kpncZ). Looking at that, I see it does a Parallel Hash Join, which looks like it should be slower, but I guess since it can do it in parallel, it winds up faster? Not sure on that, but the overall time is definitely significantly faster. Which leaves me with a couple of questions:

1) Since not using a merge join is, in this case apparently better, is there some way I can tweak the query/settings such that it knows this without having to force the merge join off?

2) If not, are there any side effects/gotchas I should be aware of if I simply call that set command before each query (or, presumably before the series of queries)?

3) Is there anything else I can do to optimize the query? I have tried using a pre-calculated point column rather than calculating ST_Point( g1.lon, g1.lat), as well as providing “false” as the last argument to ST_Distance, but while these did speed things up some, it was only about 10ms or so, so not terribly significant.

If it helps, the goal behind the somewhat odd arguments to ST_Distance is to find the component North/South and East/West distances, rather than the actual total “point a to point b” distance. Perhaps there is a better way of obtaining that goal?