pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Planner picks the wrong plan?
2+ messages / 2 participants
[nested] [flat]

* Planner picks the wrong plan?
@ 2004-10-06 00:42  Nichlas Löfdahl <crotalus@acc.umu.se>
  0 siblings, 1 reply; 2+ messages in thread

From: Nichlas Löfdahl @ 2004-10-06 00:42 UTC (permalink / raw)
  To: pgsql-performance

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))
         ->  Index Scan using users_pkey on users u  (cost=0.00..3.02 rows=1 width=86) (actual time=0.020..0.020 rows=0 loops=56)
               Index Cond: (u.uid = "outer"."owner")
               Filter: ((age > 17) AND (age < 20) AND (city = 1))
 Total runtime: 2.973 ms
(8 rows)

My question is, why doesn't the planner pick the same plan for Q1 & Q3?

/Nichlas



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Planner picks the wrong plan?
@ 2004-10-06 03:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nichlas Löfdahl <crotalus@acc.umu.se>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-10-06 03:29 UTC (permalink / raw)
  To: Nichlas Löfdahl <crotalus@acc.umu.se>; +Cc: pgsql-performance

Nichlas =?iso-8859-1?Q?L=F6fdahl?= <crotalus@acc.umu.se> writes:
> My question is, why doesn't the planner pick the same plan for Q1 & Q3?

I think it's mostly that after you've added and ANALYZEd the "age"
column, the planner has a pretty good idea of how many rows will pass
the "age > 17 AND age < 20" condition.  It can't do very much with the
equivalent condition in the original form, though, and in fact ends up
drastically underestimating the number of matching rows (86 vs reality
of 3021).  That leads directly to a bad plan choice :-(

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-10-06 03:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-06 00:42 Planner picks the wrong plan? Nichlas Löfdahl <crotalus@acc.umu.se>
2004-10-06 03:29 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox