Postgres Pro
Downloads
Home   >   mailing lists

Thread: Very slow table

Very slow table

From
Richard Weiss
Date:
Hi, I am using squeryl, which is based on jdbc, and I am trying to run the following statement:
delete from "Entry" where ("id" = 0)
but the query never seems to finish. I have tried it in both the program I am writing, and the above statement in pgadmin3, and both cases never finish.
Other queries, like
delete from "Entry" where true
complete.

The table's initialization statement is:

CREATE TABLE "Entry"
(
  "pID" bigint NOT NULL,
  id bigint NOT NULL,
  r text NOT NULL,
  CONSTRAINT "Entry_pkey" PRIMARY KEY (id), 
  CONSTRAINT "EntryFK21" FOREIGN KEY ("pID")
      REFERENCES "PP" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Entry" OWNER TO riri;

CREATE INDEX idx2eda05d9
  ON "Entry"
  USING btree
  (id);

Other information:
Postgres Version: PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit


Thanks, Richard Weiss.

Re: Very slow table

From
Szymon Guz
Date:


On 8 December 2011 06:28, Richard Weiss <richardweiss@richardweiss.org> wrote:
Hi, I am using squeryl, which is based on jdbc, and I am trying to run the following statement:
delete from "Entry" where ("id" = 0)
but the query never seems to finish. I have tried it in both the program I am writing, and the above statement in pgadmin3, and both cases never finish.
Other queries, like
delete from "Entry" where true
complete.

The table's initialization statement is:

CREATE TABLE "Entry"
(
  "pID" bigint NOT NULL,
  id bigint NOT NULL,
  r text NOT NULL,
  CONSTRAINT "Entry_pkey" PRIMARY KEY (id), 
  CONSTRAINT "EntryFK21" FOREIGN KEY ("pID")
      REFERENCES "PP" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Entry" OWNER TO riri;

CREATE INDEX idx2eda05d9
  ON "Entry"
  USING btree
  (id);

Other information:
Postgres Version: PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit


Thanks, Richard Weiss.


Hi Richard,
first of all: table is never slow - a query could be slow. Could you show us the part of application where you execute the query? 
Another reason could be some Postgres locking, check if this query hangs in pg_stat_activity table. Value 'true' in column 'waiting' means that the query hangs.
Btw, the index idx2eda05d9 is useless, defining a primary key already creates an index.
Also show us structure of the table PP, as it must be checked while deleting rows.

regards
Szymon

--
http://simononsoftware.com/