Number of rows - Mailing list pgsql-novice
From | Jana |
---|---|
Subject | Number of rows |
Date | |
Msg-id | op.uy8e891eg6o41l@truhlik Whole thread Raw |
Responses |
Re: Number of rows
|
List | 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
pgsql-novice by date: