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

Search Discussions

  • Tom Lane at Aug 25, 2009 at 10:11 pm

    Jana writes:
    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.
    Perhaps you forgot to commit the deletion?

    regards, tom lane
  • Jana Vasseru at Aug 25, 2009 at 10:37 pm
    thanks for reply, but that's not it, i am running the deletes thru a
    simple PHP script without transactions.
    and i checked that the deleted rows are non-existent over pgadmin
    Jana <jana.vasseru@gmail.com> writes:
    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.
    Perhaps you forgot to commit the deletion?

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 25, '09 at 7:38p
activeAug 25, '09 at 10:37p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jana Vasseru: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase