Thread: delete seems to be getting blocked

Em Segunda 12 Junho 2006 04:24, surabhi.ahuja escreveu:
> hi,
> I am using postgresql 8.0.0.
> i have four tables in my database
> TAB1, has one primary key T1
>
> TAB2 , has 2 fields, one is the primary ley T2 and the other one is the
> foreign key T1(from TAB1)
>
> TAB3 also has 2 fields, one is the primary ley T3 and the other is the
> foreign key T2(from TAB2)
>
> TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3)
>
> the disk is 100% full.
>
> i open psql <datbase_name> and do
> delete from TAB1
>
> nothing seems to be happening for a long time, although when i do top, it
> shows postgres taking 99%.
>
> what is happening, cant delete be quickened?

Good question.  It can be made better if you have all correct indices.  If
you're missing some of them, and if you have a huge amount of data, then it
might take a long time since for each data on your TAB1 it will have to
search *all* data on the other tables to see if the value can be removed or
not.

To know exactly what is happening, try using strace / ltrace.  You'll be able
to see where PostgreSQL is spending time and what it is doing.

--
Jorge Godoy      <godoy@metalab.unc.edu>

On Mon, Jun 12, 2006 at 12:54:15PM +0530, surabhi.ahuja wrote:
> i have four tables in my database
> TAB1, has one primary key T1
>
> TAB2 , has 2 fields, one is the primary ley T2 and the other one
> is the foreign key T1(from TAB1)
>
> TAB3 also has 2 fields, one is the primary ley T3 and the other
> is the foreign key T2(from TAB2)
>
> TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3)

Do the foreign key columns in TAB2, TAB3, and TAB4 have indexes?
How many rows do the tables have?

> the disk is 100% full.
>
> i open psql <datbase_name> and do
> delete from TAB1
>
> nothing seems to be happening for a long time, although when i do
> top, it shows postgres taking 99%.

For each record you delete in TAB1 the database must search TAB2
to check for referential integrity violations or cascading operations
(ON DELETE CASCADE, ON DELETE SET NULL, etc.).  If the foreign key
column in TAB2 doesn't have an index then each row deleted from
TAB1 will result in a sequential scan on TAB2; likewise with TAB3
if you modify TAB2 and with TAB4 if you modify TAB3.

If the tables are large then make sure you have indexes on the
foreign key columns.  If you create indexes then you might need to
start a new session due to plan caching.

--
Michael Fuhr

On Jun 15, 2006, at 8:07 AM, surabhi.ahuja wrote:

> is there any way of specifying wht type of index i want, say hash
> maps instead of the B+ trees.
> someone told me that in the case where duplicates occur(on the
> indexed field), hash map are better than B+ trees.

http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html

> and also please tell if i can assume that it will use index only
> and not go for sequential scan,
> again i was told for that i ll have to set the random page cost
> parameter to 1.

The database will use whatever it thinks is optimal. Use explain
analyze to see what it's doing.

Unless your database fits (and stays) entirely in memory, you'll
probably be pretty unhappy with random_page_cost=1.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On fös, 2006-06-16 at 11:39 +0530, surabhi.ahuja wrote:
[in response to Jim advising not to set random_page_cost=1]

> in that case, should i set
> enable_seqscan parameter to off at the time of starting postmaster?

that is unlikely to be a good stategy.

> because i have seen that even thou the index exists it still goes for
> seq scan

there can be many situations where a sequential scan
is the correct thing to do.

it is not clear whether you have a case that needs
to be optimized, or if you are just assuming that
a sequential scan must is wrong.

things that may be causing wrong choice of seqscan include:
  table has not been ANALYZED lately
  some columns need higer statistics target
  table contain few rows
  table is not correctly indexed
  search using non-indexable operators
  query phrased in a way that prevents use of indexes

so before jumping to setting wild global settings, you
should make sure you understand what your problem really
is.

the best way to do that is by looking at the output of
EXPLAIN ANALYZE.

show us the output of EXPLAIN ANALYZE <yourquery>, along
with details about relevant columns, indexes, so that we
can give more concrete advice.

gnari

>

On Jun 16, 2006, at 9:52 AM, Ragnar wrote:
> show us the output of EXPLAIN ANALYZE <yourquery>, along
> with details about relevant columns, indexes, so that we
> can give more concrete advice.

Better yet, post that to pgsql-performance, which is the most
appropriate list for this kind of thing.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461