Re: slow query performance

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.


> What is the physical size of the table and its index?

How do I find out this information?


> The output of VACUUM VERBOSE for this table would be useful to show.

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
obs=>


Cheers,
Dave.