Re: simple query runs 26 seconds - Mailing list pgsql-general

> OK, so the info relevant to this query is
>
>> INFO:  index "rid_toode_idx" now contains 1517900 row versions in 9950
>> pages
>> DETAIL:  7375 index row versions were removed.
>> 245 index pages have been deleted, 232 are currently reusable.
>
>> INFO:  "rid": found 7375 removable, 1517900 nonremovable row versions in
>> 82560 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> There were 292585 unused item pointers.
>> 18375 pages contain useful free space.
>> 0 pages are entirely empty.
>
> which gives us a density of about 150 entries/page in the index and
> 18 rows/page in the heap proper.  That seems a bit low --- do you think
> your rows are several hundred bytes each?

This table has lot of columns  [1] and database encoding is UTF-8
Most columns in every row are empty. Empty numeric columns contain 0 value,
empty string columns  NULL value mostly.
Item code and name is filled in most rows.

I don'nt know how to get table row size in disk.

> If not, the best cleanup
> strategy is probably to CLUSTER the table on whichever index you use
> most (dunno if this is your most important query or not).

RID table contains all rows from all documents in company.
It is queried and new rows are added to it frequently.

It is requently accessed by item code (toode field) and by document id
(dokumnr field).
Both of those indexes are frequently accessed.
So clustering by toode field may decrease acees speed by dokumnr field.

Anyway I run command

CLUSTER rid_toode_idx ON firma1.rid

in this night.

I'm wondering why fetching speed afects so much.

set search_path to firma1,public; select count(*)
   from rid join dok using (dokumnr)
   where toode='NE TR'
     and doktyyp='U'

returns 5
there are indexes in all fields used in join and where clauses. See note [2]
So only 5 rows need to be fetched.
No idea why fetching 5 rows requires 18 seconds.

>> postgresql.conf contains
>> autovacuum = on   # enable autovacuum subprocess?
>> However, log files does not show any autovacuum messages.
>> So I expect that autovacuum in not running.
>> Any idea why autovacuum is not running ?
>
> Did you also turn on stats_row_level and stats_start_collector?

Default postgresql.conf file installed by windows installer contains

#stats_command_string = on
#update_process_title = on
stats_start_collector = on  # needed for block or row stats
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off # (change requires restart)

So I expect that they are on.
I also ran ANALYZE manually before testing.

> It could also be that autovac *is* running but its efforts are wasted
> because of too small FSM settings --- what have you got max_fsm_pages
> set to?

postgresql.conf file contains

max_fsm_pages = 204800  # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000  # min 100, ~70 bytes each

So they are ON. I ran VACUUM and ANALYZE commands manually before testing.

Server has 1 GB RAM.
I added shared_buffers= 15000  to postgresql.conf file but speed did not
change.

Other queries for this database run fast.
Maybe I must try to re-write this query in some other way?
It seems that for some reason more than 5 rows are fetched from table.


[1] rid table structure and indexes

CREATE TABLE firma1.rid
(
  id integer NOT NULL DEFAULT nextval('rid_id_seq'::regclass),
  reanr integer NOT NULL DEFAULT nextval('rid_reanr_seq'::regclass),
  dokumnr integer NOT NULL,
  nimetus character(50),
  hind numeric(15,5) NOT NULL DEFAULT 0,
  kogus numeric(12,4) NOT NULL DEFAULT 0,
  toode character(20),
  partii character(15),
  myygikood character(4),
  hinnak character(5),
  kaubasumma numeric(15,5) NOT NULL DEFAULT 0,
  yhik character(6),
  kulukonto character(10),
  kuluobjekt character(10),
  rid2obj character(10),
  reakuupaev date,
  kogpak numeric(9,4) NOT NULL DEFAULT 0,
  kulum numeric(15,5) NOT NULL DEFAULT 0,
  baasostu numeric(15,5),
  ostuale numeric(7,2),
  rid3obj character(10),
  rid4obj character(10),
  rid5obj character(10),
  rid6obj character(10),
  rid7obj character(10),
  rid8obj character(10),
  rid9obj character(10),
  kaskogus numeric(12,4),
  aktsiis numeric(8,2),
  kulutoode character(20),
  kulupartii character(15),
  inpdoktyyp character(7),
  inpdokumnr integer,
  rtaitkogus numeric(12,4),
  fifoexpens ebool,
  calculrow ebool,
  laosumma numeric(12,2),
  laoraha character(3),
  variant ebool,
  taitmata numeric(12,4),
  iseteha ebool,
  rtellimus character(25),
  reakaal numeric(16,5),
  paritoluri character(2),
  statvaartu numeric(10,2),
  pakendilii numeric(1),
  CONSTRAINT rid_pkey PRIMARY KEY (id),
  CONSTRAINT rid_dokumnr_fkey FOREIGN KEY (dokumnr)
      REFERENCES firma1.dok (dokumnr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_hinnak_fkey FOREIGN KEY (hinnak)
      REFERENCES firma1.hkpais (hinnak) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_inpdokumnr_fkey FOREIGN KEY (inpdokumnr)
      REFERENCES firma1.dok (dokumnr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_kulukonto_fkey FOREIGN KEY (kulukonto)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_kuluobjekt_fkey FOREIGN KEY (kuluobjekt)
      REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_kulutoode_fkey FOREIGN KEY (kulutoode)
      REFERENCES firma1.toode (toode) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_laoraha_fkey FOREIGN KEY (laoraha)
      REFERENCES raha (raha) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_myygikood_fkey FOREIGN KEY (myygikood)
      REFERENCES firma1.myygikoo (myygikood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_pakendilii_fkey FOREIGN KEY (pakendilii)
      REFERENCES pakeliik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_paritoluri_fkey FOREIGN KEY (paritoluri)
      REFERENCES riik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid2obj_fkey FOREIGN KEY (rid2obj)
      REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid3obj_fkey FOREIGN KEY (rid3obj)
      REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid4obj_fkey FOREIGN KEY (rid4obj)
      REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid5obj_fkey FOREIGN KEY (rid5obj)
      REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid6obj_fkey FOREIGN KEY (rid6obj)
      REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid7obj_fkey FOREIGN KEY (rid7obj)
      REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid8obj_fkey FOREIGN KEY (rid8obj)
      REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_rid9obj_fkey FOREIGN KEY (rid9obj)
      REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_toode_fkey FOREIGN KEY (toode)
      REFERENCES firma1.toode (toode) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_yhik_fkey FOREIGN KEY (yhik)
      REFERENCES firma1.mootyhik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT rid_id_check CHECK (id > 0)
)
WITHOUT OIDS;
ALTER TABLE firma1.rid OWNER TO eeva_owner;


-- Index: firma1.rid_dokumnr_idx

-- DROP INDEX firma1.rid_dokumnr_idx;

CREATE INDEX rid_dokumnr_idx
  ON firma1.rid
  USING btree
  (dokumnr);

-- Index: firma1.rid_inpdokumnr_idx

-- DROP INDEX firma1.rid_inpdokumnr_idx;

CREATE INDEX rid_inpdokumnr_idx
  ON firma1.rid
  USING btree
  (inpdokumnr);

-- Index: firma1.rid_toode_idx

-- DROP INDEX firma1.rid_toode_idx;

CREATE INDEX rid_toode_idx
  ON firma1.rid
  USING btree
  (toode);

[2] dok table contains index

CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok)

WHERE doktyyp IN ( 'T', 'U') ;

explain analyze shows that this index is used.

It is interesting how this index can be used to optimize

WHERE dok.doktyyp='U'

clause.



Andrus.