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

Number of rows

From: Jana <jana(dot)vasseru(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Number of rows
Date: 2009-08-25 19:04:11
Message-ID: op.uy8e891eg6o41l@truhlik (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,
I have a table which shows estimated number of rows 243 655 000 it has 2  
030 460 pages. Now i'm trying to delete some of the rows, the trouble is  
that the number of rows is not getting any lower. The deletes are  
successfull and subsequent select shows that the a row was indeed deleted.  
Well i noticed that on all tables the estimated number of rows is bit  
underestimating the actual number of rows. Here on this table i cannot run  
COUNT() since, last time i tried it - it ran for about 60 hours producing  
pure nothing, according to ANALYZE the total time is still horbibble - but  
the problem is: Any execution plan on that table counts with 243 655 187  
rows - that is still the same number after i've deleted over 100m rows. So  
my question is
how many rows are really in that table?
And yes, i am running VACUUM FULL ANALYZE and it does not change a thing  
(log is below)

The definition of the table:

CREATE TABLE "public"."data_structures_items" (
   "id_data_structure_item" SERIAL,
   "id_data_structure" INTEGER NOT NULL,
   "text" VARCHAR(255) NOT NULL,
   "lft" INTEGER NOT NULL,
   "rght" INTEGER NOT NULL,
   "depth" INTEGER NOT NULL,
   "description" VARCHAR(255),
   CONSTRAINT "data_structures_items_pkey" PRIMARY  
KEY("id_data_structure_item")
)


CREATE INDEX "data_structures_items_depth" ON  
"public"."data_structures_items"
   USING btree ("depth");

CREATE INDEX "data_structures_items_id_data_structure" ON  
"public"."data_structures_items"
   USING btree ("id_data_structure");

CREATE INDEX "data_structures_items_left" ON  
"public"."data_structures_items"
   USING btree ("lft");


INFO:  vacuuming "public.data_structures_items"INFO:   
"data_structures_items": found 0 removable, 243655187 nonremovable row  
versions in 2030460 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 57 to 61 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 16244564 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 892 free bytes are potential move destinations.
CPU 6.81s/23.42u sec elapsed 207.11 sec.INFO:  index  
"data_structures_items_pkey" now contains 243655187 row versions in 668081  
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 2.70s/1.56u sec elapsed 68.78 sec.INFO:  index  
"data_structures_items_depth" now contains 243655187 row versions in  
668081 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 2.51s/1.54u sec elapsed 71.20 sec.INFO:  index  
"data_structures_items_id_data_structure" now contains 243655187 row  
versions in 668081 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 2.89s/1.51u sec elapsed 68.75 sec.INFO:  index  
"data_structures_items_left" now contains 243655187 row versions in 668081  
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 3.00s/1.68u sec elapsed 68.45 sec.
INFO:  "data_structures_items": moved 0 row versions, truncated 2030460 to  
2030460 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  vacuuming  
"pg_toast.pg_toast_16677"
INFO:  "pg_toast_16677": found 0 removable, 0 nonremovable row versions in  
0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_16677_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  analyzing "public.data_structures_items"INFO:   
"data_structures_items": scanned 30000 of 2030460 pages, containing  
3600000 live rows and 0 dead rows; 30000 rows in sample, 243655200  
estimated total rows




Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-08-25 20:59:56
Subject: Re: Number of rows
Previous:From: Jure KobalDate: 2009-08-25 17:54:41
Subject: Re: Install new perl test function in PostgreSQL

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