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
