[PERFORM] Foreign keys and partial indexes

[Nichlas Löfdahl]

Hello!

I have a partial index (btree(col)  WHERE col > 0) on table2 ('col' contains 
alot of NULL-values).

There's also a foreign key on the column pointing to the primary key of table1 
(ON UPDATE CASCADE ON DELETE SET NULL). During update/delete, it seems like it 
cannot use the partial index to find corresponding rows matching the foreign 
key (doing a full seqscan instead)? 

Is there any special reason for not letting the planner use the partial index 
when appropriate? 



\d table1
Table "public.table1"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 text   | text|
Indexes:
"table1_pkey" primary key, btree (id)

\d table2
Table "public.table2"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 col| integer |
 value  | integer |
Indexes:
"table2_pkey" primary key, btree (id)



CREATE INDEX col_part_key ON table2 USING btree(col) WHERE col > 0;
ANALYZE table2;
EXPLAIN ANALYZE DELETE FROM table2 WHERE col=1;
 QUERY PLAN
-
 Index Scan using col_part_key on table2  (cost=0.00..6.01 rows=6 width=6) 
(actual time=0.592..1.324 rows=8 loops=1)
   Index Cond: (col = 1)
 Total runtime: 4.904 ms



Delete manually WITHOUT foreign key:


test=> begin work;
BEGIN
Time: 0.808 ms
test=> explain analyze delete from table1 where id=1;
 QUERY PLAN

 Index Scan using table1_pkey on table1  (cost=0.00..3.01 rows=2 width=6) 
(actual time=0.312..0.324 rows=1 loops=1)
   Index Cond: (id = 1)
 Total runtime: 0.623 ms
(3 rows)

Time: 3.912 ms
test=> explain analyze delete from table2 where col=1;
  QUERY PLAN
---
 Index Scan using col_part_key on table2  (cost=0.00..14.70 rows=36 width=6) 
(actual time=0.338..0.557 rows=8 loops=1)
   Index Cond: (col = 1)
 Total runtime: 0.881 ms
(3 rows)

Time: 3.802 ms
test=> rollback;
ROLLBACK




Delete WITH foreign key:


test=> ALTER TABLE table2 ADD CONSTRAINT col_fkey FOREIGN KEY (col) REFERENCES 
table1(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE
Time: 3783.009 ms

test=> begin work;
BEGIN
Time: 1.509 ms
test=> explain analyze delete from table1 where id=1;
rollback;
 QUERY PLAN

 Index Scan using table1_pkey on table1  (cost=0.00..3.01 rows=2 width=6) 
(actual time=0.769..0.781 rows=1 loops=1)
   Index Cond: (id = 1)
 Total runtime: 1.027 ms
(3 rows)

Time: 3458.585 ms
test=> rollback;
ROLLBACK
Time: 1.506 ms


/Nichlas

---(end of broadcast)---
TIP 8: explain analyze is your friend

[PERFORM] Planner picks the wrong plan?

[Nichlas Löfdahl]

Hello!

I'm using Postgres 7.4.5, sort_mem is 8192. Tables analyzed / vacuumed.

Here's a function I'm using to get an age from the user's birthday:

agey(date) -> SELECT date_part('year', age($1::timestamp))


The problem is, why do the plans differ so much between Q1 & Q3 below? Something with 
age() being a non-IMMUTABLE function?


Q1: explain analyze SELECT al.pid, al.owner, al.title, al.front, al.created_at, 
al.n_images, u.username as owner_str, u.image as owner_image, u.puid as owner_puid 
FROM albums al  , users u WHERE  u.uid = al.owner AND  al.security='a' AND al.n_images 
> 0 AND date_part('year', age(u.born)) > 17 AND date_part('year', age(u.born)) < 20 
AND city = 1 ORDER BY al.id DESC LIMIT 9;
   
  QUERY PLAN   


 Limit  (cost=5700.61..5700.63 rows=9 width=183) (actual time=564.291..564.299 rows=9 
loops=1)
   ->  Sort  (cost=5700.61..5700.82 rows=83 width=183) (actual time=564.289..564.291 
rows=9 loops=1)
 Sort Key: al.id
 ->  Nested Loop  (cost=0.00..5697.97 rows=83 width=183) (actual 
time=30.029..526.211 rows=4510 loops=1)
   ->  Seq Scan on users u  (cost=0.00..5311.05 rows=86 width=86) (actual 
time=5.416..421.264 rows=3021 loops=1)
 Filter: ((date_part('year'::text, 
age((('now'::text)::date)::timestamp with time zone, (born)::timestamp with time 
zone)) > 17::double precision) AND (date_part('year'::text, 
age((('now'::text)::date)::timestamp with time zone, (born)::timestamp with time 
zone)) < 20::double precision) AND (city = 1))
   ->  Index Scan using albums_owner_key on albums al  (cost=0.00..4.47 
rows=2 width=101) (actual time=0.014..0.025 rows=1 loops=3021)
 Index Cond: ("outer".uid = al."owner")
 Filter: (("security" = 'a'::bpchar) AND (n_images > 0))
 Total runtime: 565.120 ms
(10 rows)


Result when removing the second age-check (AND date_part('year', age(u.born)) < 20):

Q2: explain analyze SELECT al.pid, al.owner, al.title, al.front, al.created_at, 
al.n_images, u.username as owner_str, u.image as owner_image, u.puid as owner_puid 
FROM albums al, users u WHERE  u.uid = al.owner AND  al.security='a' AND al.n_images > 
0 AND date_part('year', age(u.born)) > 17 AND city = 1 ORDER BY al.id DESC LIMIT 9;
-
 Limit  (cost=0.00..140.95 rows=9 width=183) (actual time=0.217..2.474 rows=9 loops=1)
   ->  Nested Loop  (cost=0.00..86200.99 rows=5504 width=183) (actual 
time=0.216..2.464 rows=9 loops=1)
 ->  Index Scan Backward using albums_id_key on albums al  (cost=0.00..2173.32 
rows=27610 width=101) (actual time=0.086..1.080 rows=40 loops=1)
   Filter: (("security" = 'a'::bpchar) AND (n_images > 0))
 ->  Index Scan using users_pkey on users u  (cost=0.00..3.03 rows=1 width=86) 
(actual time=0.031..0.031 rows=0 loops=40)
   Index Cond: (u.uid = "outer"."owner")
   Filter: ((date_part('year'::text, age((('now'::text)::date)::timestamp 
with time zone, (born)::timestamp with time zone)) > 17::double precision) AND (city = 
1))
 Total runtime: 2.611 ms
(8 rows)

Trying another approach: adding a separate "stale" age-column to the users-table:

alter table users add column age smallint;
update users set age=date_part('year'::text, age((('now'::text)::date)::timestamp with 
time zone, (born)::timestamp with time zone));
analyze users;

Result with separate column:
Q3: explain analyze SELECT al.pid, al.owner, al.title, al.front, al.created_at, 
al.n_images, u.username as owner_str, u.image as owner_image, u.puid as owner_puid 
FROM albums al  , users u WHERE  u.uid = al.owner AND  al.security='a' AND al.n_images 
> 0 AND age > 17 AND age < 20 AND city = 1 ORDER BY al.id DESC LIMIT 9;

 Limit  (cost=0.00..263.40 rows=9 width=183) (actual time=0.165..2.832 rows=9 loops=1)
   ->  Nested Loop  (cost=0.00..85925.69 rows=2936 width=183) (actual 
time=0.163..2.825 rows=9 loops=1)
 ->  Index Scan Backward using albums_id_key on albums al  (cost=0.00..2173.32 
rows=27610 width=101) (actual time=0.043..1.528 rows=56 loops=1)
   Filter: (("security" = 'a'::b