Why geometry slows down in Postgresql?

Question

PostgreSQL

In the current project, there is a need to work with coordinates, in particular, there is a task of searching between two points. Solved this problem through 2 between for x and y. Then it was required to find the nearest records to a certain point, googled geometry, point and the <-> operator. I thought about transferring the old code to this case.
Test plate:

CREATE TABLE point_test (
  id          SERIAL PRIMARY KEY,
  coordinates POINT,
  x           FLOAT,
  y           FLOAT
);
 
INSERT INTO point_test SELECT generate_series(1, 1000000), point(random() * 100, random() * 100);
UPDATE point_test SET x = coordinates [0], y = coordinates [1];
 
CREATE INDEX point_test_gist ON point_test USING GIST (coordinates);
CREATE INDEX point_test_btree ON point_test (x, y);

Query using geometry:

EXPLAIN ANALYZE
SELECT *
FROM point_test
WHERE box(point(10, 10), point(90, 90)) @> coordinates;
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on point_test  (cost=86.30..6228.15 rows=2308 width=36) (actual time=281.676..391.566 rows=639488 loops=1)
  Recheck Cond: ('(90,90),(10,10)'::box @> coordinates)
  Heap Blocks: exact=14703
  ->  Bitmap Index Scan on point_test_gist  (cost=0.00..85.72 rows=2308 width=0) (actual time=231.090..231.090 rows=1278976 loops=1)
        Index Cond: ('(90,90),(10,10)'::box @> coordinates)
Planning time: 0.112 ms
Execution time: 421.525 ms

And how it works now:

EXPLAIN ANALYZE
SELECT *
FROM point_test
WHERE x BETWEEN 10 AND 90 AND y BETWEEN 10 AND 90;
                                                                    QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on point_test  (cost=329.56..551.79 rows=58 width=36) (actual time=155.628..253.860 rows=639488 loops=1)
  Recheck Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on point_test_btree  (cost=0.00..329.55 rows=58 width=0) (actual time=153.761..153.761 rows=639488 loops=1)
        Index Cond: ((x >= 10::double precision) AND (x <= 90::double precision) AND (y >= 10::double precision) AND (y <= 90::double precision))
Planning time: 0.140 ms
Execution time: 283.563 ms

That is, on average, the increase is 100-150ms.
I do not understand the result of explain very well, but there is an idea that this is due to the fact that records are first filtered by 1 condition, and then by the second.

Answer 1

phpclub.ru/talk/threads/%D0%9F%D0%BE%D1%87%D0%B5%D...