Re: Very slow table

On 8 December 2011 06:28, Richard Weiss <richardweiss(at)richardweiss(dot)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/* <http://simononsoftware.com/>