SELECT
uuid_to_bigint(uuid) as id,
markers.uuid AS uuid,
markers.item,
markers.class,
markers.lat::float,
markers.lon::float
FROM
markers
JOIN class ON
markers.item = class.item AND
markers.class = class.class
JOIN items ON
markers.item = items.item
JOIN updates_last ON
markers.source_id = updates_last.source_id
WHERE
1=1 AND
((markers.item >= 3000 AND markers.item < 4000) OR (markers.item >= 4000 AND markers.item < 5000) OR (markers.item >= 7000 AND markers.item < 8000) OR (markers.item >= 8000 AND markers.item < 9000) OR markers.item IN (1010,1040,1150,1230,1240,1270,6020,6030,6040,9000,9001,9002,9004,9005,9006,9007,9009,9010,9011,9014,9015,9016,9017,9018,9019,9020)) AND
lonlat2z_order_curve(lon, lat) BETWEEN
zoc18min(z_order_curve(34968, 22506), 16) AND
zoc18max(z_order_curve(34968, 22506), 16) AND
lat > -90 AND
class.tags::text[] && '{geom}'::text[] AND
updates_last.timestamp > (now() - interval '3 months')
LIMIT
100
The filter on markers is quite complex and Postgres is not able to correctly estimate how many rows this filter will return:
explain (analyze) select * from markers where lonlat2z_order_curve(lon, lat) BETWEEN
zoc18min(z_order_curve(34968, 22506), 16) AND
zoc18max(z_order_curve(34968, 22506), 16) AND
lat > -90;
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Index Scan using idx_marker_z_order_curve_item on markers (cost=0.57..813967.83 rows=410395 width=763) (actual time=5.068..12.109 rows=30 loops=1)
Index Cond: ((z_order_curv
As he thinks he will get many rows, he estimates better to fetch first class and item and join markers table. Instead of a first read the markers table and then join other table.
anayrat
commented
1 year ago
Hello, I noticed this expensive query:
Here is the plan : https://explain.depesz.com/s/kBBM#html
The filter on markers is quite complex and Postgres is not able to correctly estimate how many rows this filter will return:
As he thinks he will get many rows, he estimates better to fetch first class and item and join markers table. Instead of a first read the markers table and then join other table.