Re: slow query performance

Dave Weaver wrote:

> Tom Lane wrote:
>
>>Do you do a lot of updates or deletes on this table, or is it just
>>inserts?
>
>
> Inserts and updates. No deletes.

Updates are insert/deletes under postgresql as it does not updates rows in place.

>>What is the physical size of the table and its index?
> How do I find out this information?

cd $PGDATA;du -h

This will give you size of each directory. Using utility oid2name in contrib
module in sources, you can find out what object is stored in which file. Same is
true for data files under it as well.

> obs=> vacuum verbose obs;
> NOTICE: --Relation obs--
> NOTICE: Pages 276896: Changed 2776, reaped 67000, Empty 0, New 0;s
> Tup 13739326: Vac 78031, Keep/VTL 3141/3141, Crash 0, UnUsed 303993,
> MinLen 72, MaxLen 476; Re-using: Free/Avail. Space 16174372/14995020;
> EndEmpty/Avail. Pages 0/18004. CPU 26.11s/3.78u sec.
> NOTICE: Index obs_pkey: Pages 114870; Tuples 13739326: Deleted 37445. CPU
> 12.33s/39.86u sec.
> NOTICE: Index obs_valid_time: Pages 45713; Tuples 13739326: Deleted 37445.
> CPU 4.38s/37.65u sec.
> NOTICE: InvalidateSharedInvalid: cache state reset
> NOTICE: Index obs_station: Pages 53170; Tuples 13739326: Deleted 37445. CPU
> 6.46s/56.63u sec.
> NOTICE: Rel obs: Pages: 276896 --> 275200; Tuple(s) moved: 30899. CPU
> 33.94s/51.05u sec.
> NOTICE: Index obs_pkey: Pages 114962; Tuples 13739326: Deleted 30881. CPU
> 13.24s/19.80u sec.
> NOTICE: Index obs_valid_time: Pages 45819; Tuples 13739326: Deleted 30881.
> CPU 4.51s/17.42u sec.
> NOTICE: Index obs_station: Pages 53238; Tuples 13739326: Deleted 30881. CPU
> 5.78s/18.33u sec.
> NOTICE: --Relation pg_toast_503832058--
> NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
> Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
> NOTICE: Index pg_toast_503832058_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u
> sec.
> VACUUM

You reindexed? Seems like this is after you have already run a vacuum. So not
much change is shown here.

HTH

Shridhar