Skip to content

Instantly share code, notes, and snippets.

/gist:d6c9b183196717d73b6a Secret

Created October 22, 2008 21:08
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/d6c9b183196717d73b6a to your computer and use it in GitHub Desktop.
Save anonymous/d6c9b183196717d73b6a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
ey05-s00326 ~ # createdb cotest
ey05-s00326 ~ # pg_restore /data/coordinate_test.dmp | psql cotest
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
ey05-s00326 ~ # psql cotest
Welcome to psql 8.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
cotest=# ANALYZE ;
ANALYZE
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-01-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..2.23 rows=1 width=120) (actual time=0.000..0.000 rows=1 loops=1)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.000 ms
(3 rows)
cotest=# set enable_seqscan = 0;
SET
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_coordinates_ix on test (cost=0.00..8.27 rows=1 width=120) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.000 ms
(4 rows)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-01-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# UPDATE test SET created_at = '2/1/2008' WHERE id = 1;
UPDATE 1
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+-------------+------------+------------
(0 rows)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_coordinates_ix on test (cost=0.00..8.27 rows=1 width=120) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.000 ms
(4 rows)
cotest=# REINDEX INDEX test_coordinates_ix ;
REINDEX
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_coordinates_ix on test (cost=0.00..8.27 rows=1 width=120) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.000 ms
(4 rows)
cotest=# UPDATE test SET created_at = '2/2/2008' WHERE id = 1;
UPDATE 1
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-02 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+-------------+------------+------------
(0 rows)
cotest=# DROP INDEX test_coordinates_ix;
DROP INDEX
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-02 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
============================
ey01-s00347 ~ # psql cotest
Welcome to psql 8.2.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
cotest=# ANALYZE ;
ANALYZE
cotest=# show server_version;
server_version
----------------
8.2.7
(1 row)
cotest=# select postgis_version();
postgis_version
---------------------------------------
1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
cotest=# SELECT id FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id
----
1
(1 row)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-01-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# EXPLAIN ANALYZE SELECT id FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..3.23 rows=1 width=4) (actual time=0.099..0.100 rows=1 loops=1)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.132 ms
(3 rows)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..3.23 rows=1 width=45) (actual time=0.098..0.099 rows=1 loops=1)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.128 ms
(3 rows)
cotest=# UPDATE test SET created_at = '2/1/2008' where id = 1;
UPDATE 1
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..3.23 rows=1 width=45) (actual time=0.099..0.100 rows=1 loops=1)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.128 ms
(3 rows)
cotest=# set enable_seqscan = 0;
SET
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using test_coordinates_ix on test (cost=0.00..8.27 rows=1 width=45) (actual time=0.065..0.068 rows=1 loops=1)
Index Cond: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.127 ms
(4 rows)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
id | coordinates | created_at | updated_at
----+----------------------------------------------------+---------------------+----------------------------
1 | 0101000020E6100000DE205A2BDA5856C0685C381092894540 | 2008-02-01 00:00:00 | 2008-10-21 21:40:17.071026
(1 row)
cotest=# EXPLAIN ANALYZE SELECT * FROM test WHERE coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using test_coordinates_ix on test (cost=0.00..8.27 rows=1 width=45) (actual time=0.059..0.061 rows=1 loops=1)
Index Cond: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Filter: (coordinates && '0101000020E6100000DE205A2BDA5856C0685C381092894540'::geometry)
Total runtime: 0.101 ms
(4 rows)
cotest=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment