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'::bpchar) AND (n_images 0))
-
