如何根据输入在索引操作和非索引操作之间切换?

问题描述

我之前的问题发现，导致性能出现问题的原因是因为在Postgis中使用索引距离运算符在返回很多行的大区域上非常慢.

My previous question discovered that the cause of my performance troubles is because using the indexed distance operator in Postgis is very slow on large areas that return many rows.

因此，似乎理想的解决方案可能是选择一个任意的英里距离量，然后在该范围内(例如，低于75英里)使用索引的距离运算符(ST_DWithin)，然后超出该范围(因此等于或等于75英里)时，请使用非索引距离运算符(<->)

As a result, it seems the ideal solution may be to pick some arbitrary miles distance amount and then use the indexed distance operator (ST_DWithin) when it's in that range (let's say, below 75 miles), and then to use the non-indexed distance operator (<->) when it's above that range (so, 75 miles or above)

我当前的功能如下:

CREATE OR REPLACE FUNCTION public.usp_get_data(i_distance_choice integer, i_longitude double precision, i_latitude double precision)
 RETURNS TABLE(convo_id bigint)
 LANGUAGE SQL STABLE
AS $function$
    SELECT po.convo_id
    FROM post po
    WHERE ST_DWithin(po.geog, ST_SetSRID(ST_MakePoint(i_longitude, i_latitude), 4326)::geography, i_distance_choice * 1609.34)
    ORDER BY po.reply_count DESC, convo_id DESC
    LIMIT 10;
$function$;

所以看起来我需要某种条件的.也就是说，如果i_distance_choice低于75，则使用ST_DWithin，然后使用75或更高，则使用<->运算符.

So it looks like I need to have some sort of conditional. That is, if i_distance_choice is below 75, to use ST_DWithin, and then if it is 75 or above, then to use the <-> operator instead.

是否可以使用SQL lanaguage做到这一点，还是我必须使用诸如plsql之类的东西?还是有一种更好的方法可以通知Postgres不要将索引用于足够大的输入?这样甚至不需要条件吗?

Is there a way to do this with the SQL lanaguage, or would I have to use something like plsql instead? Or is there an even better way where we can inform Postgres to not use the index for sufficiently large inputs? So that the conditional is not even required?

推荐答案

我认为无法在纯SQL中完成.

I don't think it can be done in pure SQL.

将其转换为PL/pgSQL非常简单.

It is pretty straightforward to translate this to PL/pgSQL.

CREATE OR REPLACE FUNCTION public.usp_get_data(i_distance_choice integer, i_longitude double precision, i_latitude double precision)
 RETURNS TABLE(convo_id bigint)
 LANGUAGE plpgsql
 STABLE
AS $function$
    BEGIN
      IF i_distance_choice < 75 then
        return query SELECT po.convo_id
          FROM post po
          WHERE ST_DWithin(po.geog, ST_SetSRID(ST_MakePoint(i_longitude, i_latitude), 4326), i_distance_choice * 1609.34)
          ORDER BY po.reply_count DESC, convo_id DESC
          LIMIT 10;
      ELSE
        return query SELECT po.convo_id
          FROM post po
          WHERE po.geog<->ST_SetSRID(ST_MakePoint(i_longitude, i_latitude), 4326) < i_distance_choice * 1609.34
          ORDER BY po.reply_count DESC, convo_id DESC
          LIMIT 10;
      END IF;
    END
$function$

我验证了它使用的地理索引< 75和btree (reply_count, convo_id)索引为75及更高.

I verified it uses the geography index <75 and the btree (reply_count, convo_id) index at 75 and above.

这篇关于如何根据输入在索引操作和非索引操作之间切换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！