这个查询应该有很长的路要走(是much快点):
WITH school AS (
SELECT s.osm_id AS school_id, text 'school' AS type, s.osm_id, s.name, s.way_geo
FROM planet_osm_point s
, LATERAL (
SELECT 1 FROM planet_osm_point
WHERE ST_DWithin(way_geo, s.way_geo, 500, false)
AND amenity = 'bar'
LIMIT 1 -- bar exists -- most selective first if possible
) b
, LATERAL (
SELECT 1 FROM planet_osm_point
WHERE ST_DWithin(way_geo, s.way_geo, 500, false)
AND amenity = 'restaurant'
LIMIT 1 -- restaurant exists
) r
WHERE s.amenity = 'school'
)
SELECT * FROM (
TABLE school -- schools
UNION ALL -- bars
SELECT s.school_id, 'bar', x.*
FROM school s
, LATERAL (
SELECT osm_id, name, way_geo
FROM planet_osm_point
WHERE ST_DWithin(way_geo, s.way_geo, 500, false)
AND amenity = 'bar'
) x
UNION ALL -- restaurants
SELECT s.school_id, 'rest.', x.*
FROM school s
, LATERAL (
SELECT osm_id, name, way_geo
FROM planet_osm_point
WHERE ST_DWithin(way_geo, s.way_geo, 500, false)
AND amenity = 'restaurant'
) x
) sub
ORDER BY school_id, (type <> 'school'), type, osm_id;
This is not与您的原始查询相同,而是您真正想要的,根据评论中的讨论:
我想要一份拥有 500 家以内餐厅和酒吧的学校列表
米,我需要每个学校的坐标及其对应的
餐厅和酒吧。
因此,此查询返回这些学校的列表,然后是附近的酒吧和餐馆。每组行都由osm_id
栏中的学校school_id
.
现在使用LATERAL
连接,以利用空间 GiST 索引。
TABLE school
只是简写SELECT * FROM school
:
- psql 中有 SELECT * FROM 的快捷方式吗?
表达方式(type <> 'school')
首先对每组中的学校进行排序,因为:
子查询sub
在决赛中SELECT
只需要通过这个表达式来排序。 AUNION
查询限制附加的ORDER BY
仅列出列,无表达式。
我重点关注您为了回答这个问题而提出的问题 -ignoring过滤其他 70 个文本列中任何一个的扩展要求。这确实是一个设计缺陷。搜索条件应集中于few列。或者您必须对所有 70 列建立索引,而像我要建议的多列索引几乎不是一个选择。仍然possible尽管 ...
Index
除了现有的:
"idx_planet_osm_point_waygeo" gist (way_geo)
如果总是在同一列上进行过滤,您可以创建一个多列索引涵盖您感兴趣的几列,所以仅索引扫描成为可能:
CREATE INDEX planet_osm_point_bar_idx ON planet_osm_point (amenity, name, osm_id)
Postgres 9.5
即将推出的 Postgres9.5介绍主要改进这恰好解决了您的情况:
-
允许查询使用 GiST 索引对边界框索引对象(多边形、圆形)执行精确的距离过滤
(亚历山大·科罗特科夫、海基·林纳坎加斯)
以前,需要一个公共表表达式来返回一个大的
按边界框距离排序然后过滤的行数
进一步进行更准确的非边界框距离计算。
允许 GiST 索引执行仅索引扫描 (Anastasia Lubennikova, Heikki Linnakangas, Andreas Karlsson)
您对此特别感兴趣。现在你可以拥有一个single多列(覆盖)GiST 索引:
CREATE INDEX reservations_range_idx ON reservations
USING gist(amenity, way_geo, name, osm_id)
And:
- 提高位图索引扫描性能(Teodor Sigaev、Tom Lane)
And:
- 添加GROUP BY分析功能
GROUPING SETS
, CUBE
and ROLLUP
(安德鲁·吉尔斯、阿特里·夏尔马)
为什么?因为ROLLUP会简化我建议的查询。相关回答:
- PostgreSQL 中的 Grouping() 等效项?
第一个 alpha 版本已于 2015 年 7 月 2 日发布。预计发布时间表:
这是9.5版本的alpha版本,表明一些变化
在发布之前功能仍然是可能的。 PostgreSQL 项目
将于8月份发布9.5 beta 1,之后定期发布
测试所需的额外测试版,直到最终版本发布
2015年底。
Basics
当然,一定不要忽视基础知识:
- PostgreSQL Wiki 上的慢查询问题页面