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
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
Its still slow!
In response to
pgsql-hackers by date
|Next:||From: Alvaro Herrera||Date: 2004-05-26 22:03:41|
|Subject: Nested xacts: looking for testers and review|
|Previous:||From: Gaetano Mendola||Date: 2004-05-26 21:17:08|
|Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow|