1

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.

7
  • 1
    NB A limit clause without an order byclause is meaningless. If the table is updated/exported/imported/partitioned etc the order rows will be returned in can change. Mar 16, 2021 at 11:11
  • 1
    use-the-index-luke.com/no-offset
    – user1822
    Mar 16, 2021 at 11:25
  • 1
    It seems your table is severely bloated. That many shared buffer hits for only 8455 rows looks bad. Run vacuum full some_table; then try again
    – user1822
    Mar 16, 2021 at 11:27
  • @a_horse_with_no_name thanks for the suggestion if it's some kind of attack, what is the way to figure out rows that contain most data? is there any command to sort it out by row size? In other words, I want to find out what rows contain most of the data. Mar 16, 2021 at 11:43
  • 2
    A bloated table has nothing to do with an "attack". It simply means that for some reason autovacuum wasn't able to clean up properly. More often than not this is caused by not ending transactions properly. vacuum full will remove that bloat. The Postgres Wiki contains some queries to investigate this: wiki.postgresql.org/wiki/Show_database_bloat
    – user1822
    Mar 16, 2021 at 11:45

2 Answers 2

1

a_horse_with_no_name says:

It seems your table is severely bloated. That many shared buffer hits for only 8455 rows looks bad. Table bloat means that for some reason autovacuum wasn't able to clean it up. More often than not this is caused by not ending transactions properly.

The Postgres Wiki contains some queries to investigate the bloat.

Run vacuum full some_table, which will remove that bloat, then try your query again.

0

When you use the LIMIT clause, it is applied after all the data is loaded to filter down the data, which is evident by your EXPLAIN as it is the last operation (top of the operation chain). This is because LIMIT isn't a predicate filter, rather it just takes the top N number of rows from the total result set. While 8,455 rows is tiny, the actual Table size is 62 GB on disk which is a decent amount of data to SELECT all at once and then filter and return to the client.

When you use the WHERE clause, the filtering is applied upfront, and can be done efficiently because your id field is indexed, so very few rows need to actually be analyzed to serve the data (since the logical data structure is a B-Tree) and therefore much less data (in file size) to load and process.

If you want a more efficient way to get only 50 records back, you'll need to find a way to use predicate filtering with your WHERE clause to reduce the data first, and then apply your TOP if needed. E.g. if you know you which range of ids to filter on (though I'm assuming since you're using a GUID that'll be hard to do). If there are other fields you can use to help filter down the data with predicate filtering, then you should create indexes on those fields as well, but that will depend on your table definition.

3
  • 1
    The actual stats says only 10 rows were actually read from the table: Seq Scan on project_views: ...(actual time=363665.381..363668.358 rows=10 loops=1). Suggesting that the limit clause was sensibly applied as soon as it could be. Mar 16, 2021 at 11:56
  • @AndrewSayer Yes and no. The entire set of data still needed to be loaded from disk first before it could be applied, there's no way around that (without use of an index). Which again, isn't a lot of rows per se, but the table itself is big enough in size when being fully loaded from disk, to make a difference. Perhaps a_horse_with_no_name's suggestion of VACUUMing the table will correct that issue though.
    – J.D.
    Mar 16, 2021 at 12:00
  • @J.D. please provide a source for this claim (that the entire set of data is needed to be loaded from disk first before LIMIT can be applied). It sounds completely wrong. Mar 16, 2021 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.