Re: Very slow table - Mailing list pgsql-general

From Szymon Guz
Subject Re: Very slow table
Date
Msg-id CAFjNrYuHyPD+ORNELbVLUTNxEUjf8rXs0C5f305kQVxCnNGdMQ@mail.gmail.com
Whole thread Raw
In response to Very slow table  (Richard Weiss <richardweiss@richardweiss.org>)
List pgsql-general


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/

pgsql-general by date:

Previous
From: "Nikhil G. Daddikar"
Date:
Subject: Error while vacuuming table
Next
From: Craig Ringer
Date:
Subject: Re: Hope for a new PostgreSQL era?