Skip site navigation (1) Skip section navigation (2)

Re: SELECT * FROM <table> LIMIT 1; is really slow

From: David Blasby <dblasby(at)refractions(dot)net>
To: Gaetano Mendola <mendola(at)bigfoot(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Date: 2004-05-26 21:55:27
Message-ID: 40B5124F.2020504@refractions.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Gaetano Mendola wrote:

> David Blasby wrote:
> 
>> I have a table with about 16,000,000 rows in it.
>>
>> When I do a:
>>
>> SELECT * FROM <table> LIMIT 1;
>>
>> it takes about 10 minutes (thats about how long it takes to do a full 
>> sequential scan).
>>
>> I had originally thought that there might be a large number of 
>> "wasted/retired" tuples in the table so I "vacuum analysed" the 
>> database.  It had no effect.  I had a "vacuum full" going on the table 
>> for 17 hours before I killed it.
> 
> 
> Are you sure that the vacuum full was running or sitting there to
> wait an idle transaction ?

It was running at about 90% CPU.

I just did another vacuum analyse on the table:

cwb_prod_5_20=# VACUUM ANALYZE verbose csn_edges;
INFO:  vacuuming "public.csn_edges"
INFO:  index "csn_edges_group_code_idx" now contains 16289929 row 
versions in 75789 pages
INFO:  index "csn_edges_edge_id_idx" now contains 16289929 row versions 
in 55210 pages
INFO:  index "csn_edges_code_idx" now contains 16289929 row versions in 
61203 pages
INFO:  index "csn_edges_outside_idx" now contains 16289929 row versions 
in 75719 pages

INFO:  index "csn_edges_the_geom_idx" now contains 16289929 row versions 
in 238795 pages
INFO:  "csn_edges": found 0 removable, 16289929 nonremovable row 
versions in 2783986 pages
INFO:  vacuuming "pg_toast.pg_toast_126945560"
INFO:  index "pg_toast_126945560_index" now contains 441432 row versions 
in 3064 pages
INFO:  "pg_toast_126945560": found 0 removable, 441432 nonremovable row 
versions in 154691 pages
INFO:  analyzing "public.csn_edges"

INFO:  "csn_edges": 2783986 pages, 3000 rows sampled, 6724 estimated 
total rows
VACUUM


Its still slow!

dave

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2004-05-26 22:03:41
Subject: Nested xacts: looking for testers and review
Previous:From: Gaetano MendolaDate: 2004-05-26 21:17:08
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group