a very simple postgres query, like SELECT
takes five minutes to execute. It was working fine, taking less than one second and at some point the execution time increased significantly to five minutes. When I do SELECT
query to another table in the same database - it works pretty fast. Only one table is affected and it seems to happen at some point, before it was also executing fast. Following query takes five minutes to execute:
SELECT * FROM some_table LIMIT 50 OFFSET 0;
another thing is that if I select by Id - it works instantly, very fast, less than one second:
SELECT * FROM some_table WHERE id = '33ae0b5f0d6a0435e36faf8d';
UPDATE 1
table definition has 34 columns,
Id primary key column is indexed, it is GUID not integer
table contains only 8455 rows
server_prod=# SELECT COUNT(*) FROM some_table;
count
-------
8455
(1 row)
UPDATE 2
server_prod=# select current_setting('shared_buffers') AS shared_buffers, pg_size_pretty(pg_table_size('some_table')) AS table_size;
shared_buffers | table_size
----------------+------------
128MB | 62 GB
(1 row)
server_prod=# explain (analyze, buffers, timing) SELECT * FROM some_table LIMIT 10 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..9677.70 rows=10 width=1379) (actual time=363665.384..363668.365 rows=10 loops=1)
Buffers: shared hit=5701604 read=1443335
-> Seq Scan on project_views (cost=0.00..8109910.80 rows=8380 width=1379) (actual time=363665.381..363668.358 rows=10 loops=1)
Buffers: shared hit=5701604 read=1443335
Planning time: 0.099 ms
Execution time: 363668.414 ms
(6 rows)
UPDATE 3
psql version is 10.7
server_prod=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)
please advise.
limit
clause without anorder by
clause is meaningless. If the table is updated/exported/imported/partitioned etc the order rows will be returned in can change.vacuum full some_table;
then try againvacuum full
will remove that bloat. The Postgres Wiki contains some queries to investigate this: wiki.postgresql.org/wiki/Show_database_bloat