Postgresql - slow query "select * from table limit 1"
I am using PostgreSQL 9.3. I have a database with one table and this table has over 600 million rows. When I connect to this database, the first request is very slow:
explain analyze select * from request_log limit 1;
Limit (cost=0.00..0.02 rows=1 width=61) (actual time=481439.127..481439.129 rows=1 loops=1)
-> Seq Scan on request_log (cost=0.00..13996870.79 rows=651159679 width=61) (actual time=481439.123..481439.123 rows=1 loops=1)
Total runtime: 481440.488 ms
I don't understand why the seq scan just doesn't stop after reading the first line?
source to share
I think you are misreading the result of the analysis of the explanations.
The line "Limit (cost = 0.00..0.02 rows = 1 width = 61) (actual time = 481439.127..481439.129 rows = 1 loops = 1)" indicates that it has limited sequential scans.
If you try it without limit
EXPLAIN ANALYSE SELECT * FROM request_log;
you will probably see that it will take much longer.
The problem is with fetching data in your database. Check the postgresql.conf file under "Resource Utilization" and "QUERY TUNING" for anything unusual. Check that postgresql log is nothing out of the ordinary.
source to share