[PERFORM] forced sequential scan when condition has current_user

[Keresztury Balázs]

hi,

just a small question: is it normal that PostgreSQL 8.4.1 always uses
sequential scanning on any table when there is a condition having the
constant current_user? Of course there is a btree index set on that table,
but the DBMS just doesn't want to utilize it. When I replace current_user to
any string, the planner uses the index normally.

I can demonstrate it with the following simple query:

SELECT psz.kotesszam FROM projekt.projektszervezet psz WHERE
psz.felhasznalo_id = current_user;

Explain analyze:

Seq Scan on projektszervezet psz  (cost=0.00..255.07 rows=42 width=9)
  Filter: ((felhasznalo_id)::name = current_user())

Metadata:

CREATE TABLE projekt.projektszervezet (
  CONSTRAINT projektszervezet_pkey PRIMARY KEY(kotesszam,
felhasznalo_id),
  CONSTRAINT projektszervezet_fk_felhasznalo FOREIGN KEY
(felhasznalo_id)
REFERENCES felhasznalo.felhasznalo(felhasznalo_id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE,
  CONSTRAINT projektszervezet_fk_projekt FOREIGN KEY (kotesszam)
REFERENCES projekt.projekt(kotesszam)
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE,
  CONSTRAINT projektszervezet_fk_szerep FOREIGN KEY (szerep_id)
REFERENCES felhasznalo.szerep(szerep_id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
) INHERITS (projekt.projektszervezet_sablon)
WITH OIDS;

CREATE INDEX projektszervezet_idx_felhasznalo_id ON
projekt.projektszervezet
  USING btree (felhasznalo_id);

CREATE INDEX projektszervezet_idx_kotesszam ON
projekt.projektszervezet
  USING btree (kotesszam);

CREATE TRIGGER projektszervezet_archivalas BEFORE UPDATE OR DELETE ON
projekt.projektszervezet FOR EACH ROW EXECUTE PROCEDURE
public.projektszervezet_archivalas_trigger();

CREATE TRIGGER projektszervezet_idopecset BEFORE UPDATE ON
projekt.projektszervezet FOR EACH ROW EXECUTE PROCEDURE
public.idopecset_trigger();

CREATE TRIGGER projektszervezet_naplozas BEFORE INSERT OR UPDATE OR DELETE
ON projekt.projektszervezet FOR EACH ROW EXECUTE PROCEDURE
public.projektszervezet_naplozas_trigger();

Inherited table:

CREATE TABLE projekt.projektszervezet_sablon (
  kotesszam VARCHAR(10) NOT NULL,
  felhasznalo_id VARCHAR NOT NULL,
  szerep_id VARCHAR(3),
  felvivo VARCHAR DEFAULT current_user() NOT NULL,
  felvitel_idopont TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  modosito VARCHAR,
  modositas_idopont TIMESTAMP WITHOUT TIME ZONE,
  elso_felvivo VARCHAR DEFAULT current_user() NOT NULL,
  elso_felvitel_idopont TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now() NOT
NULL
) WITH OIDS;

CREATE TRIGGER projektszervezet_idopecset BEFORE UPDATE ON
projekt.projektszervezet_sablon FOR EACH ROW EXECUTE PROCEDURE
public.idopecset_trigger();


Thanks!
Balazs


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] forced sequential scan when condition has current_user

[Keresztury Balázs]

Actually table itself has ~8000 records. I don't know why does it report 42
rows, since there is not even a matching row in the table for this specific
condition.. But as we all know, the universal answer for every question is
42 ;) Autovacuum is on, and I also did some vacuuming before I started to
play with this query.

I could implement a function into my application to replace current_user to
the actual username, but it just doesn't worth it. By the way, replacing
current_user to a text constant reduces cost from 255-72, so there is a
significant difference. Don't you think this is actually a bug, not a
feature?

balazs

-Original Message-
From: Robert Haas [mailto:robertmh...@gmail.com] 
Sent: Monday, January 04, 2010 10:59 PM
To: Keresztury Balázs
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] forced sequential scan when condition has
current_user

2010/1/4 Keresztury Balázs bal...@gaslightmusic.hu:
 just a small question: is it normal that PostgreSQL 8.4.1 always uses
 sequential scanning on any table when there is a condition having the
 constant current_user? Of course there is a btree index set on that
table,
 but the DBMS just doesn't want to utilize it. When I replace current_user
to
 any string, the planner uses the index normally.

 I can demonstrate it with the following simple query:

 SELECT psz.kotesszam FROM projekt.projektszervezet psz WHERE
 psz.felhasznalo_id = current_user;

 Explain analyze:

 Seq Scan on projektszervezet psz  (cost=0.00..255.07 rows=42 width=9)
   Filter: ((felhasznalo_id)::name = current_user())

You've only got 42 rows in that table - PostgreSQL probably thinks a
sequential scan will be faster.  It might even be right.  The thing
is, PostgreSQL doesn't know at planning time what the value of
current_user() will be, so the plan can't depend on that; the planner
just takes its best shot.  But if you provide a particular value in
the query then it will look at the stats and see what seems to make
the most sense for that particular value.  So using one of the more
commonly-occuring value in the table might produce a sequential scan,
while a less common value might lead to an index scan.

...Robert


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance