Re: Force another plan. - Mailing list pgsql-performance

Tom Lane skrev:
> Fredrik Olsson <fredrik.olsson@treyst.se> writes:
>
>> I have some quite huge queries, inside functions, so debugging is kind
>> of hard. But I have located the query that for some reason gets 4 times
>> as slow after an analyze.
>>
>
> Could we see EXPLAIN ANALYZE output for these cases, not just EXPLAIN?
> It seems a bit premature to be discussing ways to "force" a plan choice
> when you don't even have a clear idea what's going wrong.
>
Sorry about that, my fault. Here comes EXPLAIN ANALYZE:

Before VACUUM ANALYZE:
===
Nested Loop  (cost=16.80..34.33 rows=1 width=28) (actual
time=54.197..98.598 rows=1 loops=1)
  Join Filter: (ischildof(2, "outer".calendar) OR (hashed subplan))
  ->  Nested Loop  (cost=0.00..11.66 rows=1 width=32) (actual
time=0.307..0.458 rows=3 loops=1)
        ->  Index Scan using t_events_eventype on t_events e
(cost=0.00..5.82 rows=1 width=28) (actual time=0.241..0.307 rows=3 loops=1)
              Index Cond: (eventtype = 1)
              Filter: (rrfreq IS NOT NULL)
        ->  Index Scan using t_entities_pkey on t_entities te
(cost=0.00..5.83 rows=1 width=4) (actual time=0.035..0.039 rows=1 loops=3)
              Index Cond: (te."ID" = "outer".entity)
              Filter: (partof = 'events'::name)
  ->  Index Scan using t_entities_pkey on t_entities  (cost=0.00..5.85
rows=1 width=4) (actual time=28.445..28.447 rows=0 loops=3)
        Index Cond: (t_entities."ID" = "outer".entity)
        Filter: ((haveaccess(createdby, responsible, "class", false) OR
CASE WHEN (partof = 'contacts'::name) THEN ischildof(ancestorof(me()),
"ID") ELSE false END) AND (subplan))
        SubPlan
          ->  Function Scan on alleventoccurances  (cost=0.00..12.50
rows=1000 width=8) (actual time=19.745..19.745 rows=0 loops=3)
  SubPlan
    ->  Seq Scan on t_attendees  (cost=0.00..16.38 rows=170 width=4)
(actual time=0.422..0.447 rows=2 loops=1)
          Filter: ischildof(2, contact)
Total runtime: 99.814 ms

After VACUUM ANALYZE:
===
Nested Loop  (cost=2.11..4.92 rows=1 width=28) (actual
time=434.321..439.102 rows=1 loops=1)
  Join Filter: ("inner"."ID" = "outer"."ID")
  ->  Hash Join  (cost=2.11..3.67 rows=1 width=32) (actual
time=434.001..438.775 rows=1 loops=1)
        Hash Cond: ("outer"."ID" = "inner".entity)
        Join Filter: (ischildof(2, "inner".calendar) OR (hashed subplan))
        ->  Seq Scan on t_entities  (cost=0.00..1.49 rows=7 width=4)
(actual time=404.539..409.302 rows=2 loops=1)
              Filter: ((haveaccess(createdby, responsible, "class",
false) OR CASE WHEN (partof = 'contacts'::name) THEN
ischildof(ancestorof(me()), "ID") ELSE false END) AND (subplan))
              SubPlan
                ->  Function Scan on alleventoccurances
(cost=0.00..12.50 rows=1000 width=8) (actual time=27.871..27.871 rows=0
loops=14)
        ->  Hash  (cost=1.07..1.07 rows=3 width=28) (actual
time=0.063..0.063 rows=3 loops=1)
              ->  Seq Scan on t_events e  (cost=0.00..1.07 rows=3
width=28) (actual time=0.023..0.034 rows=3 loops=1)
                    Filter: ((rrfreq IS NOT NULL) AND (eventtype = 1))
        SubPlan
          ->  Seq Scan on t_attendees  (cost=0.00..1.02 rows=1 width=4)
(actual time=0.205..0.228 rows=2 loops=1)
                Filter: ischildof(2, contact)
  ->  Seq Scan on t_entities te  (cost=0.00..1.18 rows=6 width=4)
(actual time=0.029..0.045 rows=6 loops=1)
        Filter: (partof = 'events'::name)
Total runtime: 440.385 ms

As I read it, the villain is the sequential sqan on t_entities with the
huge filter; haveacces() ...
And that is understandable, doing haveaccess() on all rows is not good.
A  much better solution in this case would be to first get the set that
conforms to (partof = 'events'::name), that would reduce the set to a
third. Secondly applying (eventtype=1) would reduce that to half.  Then
it is time to do the  (haveaccess() ...).

Perhaps a small explanation of the tables, and their intent is in order.

What I have is one "master table" with entities (t_entitites), and two
child tables t_contacts and t_events. In a perfect world rt_contacts and
t_events would have inherited from t_entities as they share muuch data,
but then I would not be able to have foreign key referencing just
events, or just contacts. So instead every row in events and contacts
have a corresponding one to one row in entities. The fourth table used
in this query is t_attendees, that links well attendees for an event to
contacts. Here goes a simplified example (SQL says more then 1000 words):

CREATE TABLE t_entities (
  "ID" integer PRIMARY KEY,
  "createdby" integer NOT NULL,
  "class" integer NOT NULL,  -- Defines visibility for entity and is
used by haveaccess()
  "partof" name NOT NULL
);

CREATE TABLE t_contacts (
  "entity" integer PRIMARY KEY REFERENCES t_entities ("ID"),
  "undercontact" integer REFERENCES t_contacts ("entity"), -- Tree
structure, used by haveaccess()
  "name" varchar(48)
);

ALTER TABLE t_entities ADD FOREIGN KEY ("createdby")
  REFERENCES t_contacts ("entity");

CREATE TABLE t_events (
  "entity" integer PRIMARY KEY REFERENCES t_entities ("ID"),
  "calendar" integer NOT NULL REFERENCES t_entities ("ID"),
  "eventtype" integer NOT NULL,
  "start" timestamptz,
  "end" timestamptz
);

CREATE TABLE t_attendees (
  "event" integer NOT NULL REFERENCES t_events ("entity"),
  "contact" integer NOT NULL REFERENCES t_contacts ("entity"),
  PRIMARY KEY ("event", "contact")
);

No user have privileges to select, update or delete in any of these
tables. They are instead accessed with views that are made updatable
using rules. These rules uses the function haveaccess() to sort out the
rows that each user is allowed to see. Users are also contacts in the
t_contacts table.

Well the complete database, with a shell script for setting it up can as
I said be provided if wanted.

--
//Fredrik Olsson
  Treyst AB
  +46-(0)19-362182
  fredrik.olsson@treyst.se