pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nichlas Löfdahl <crotalus@acc.umu.se>
To: pgsql-performance@postgresql.org
Subject: Planner picks the wrong plan?
Date: Wed, 6 Oct 2004 02:42:04 +0200
Message-ID: <20041006004204.GA13074@shaka.acc.umu.se> (raw)

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



view thread (2+ messages)  latest in thread

Message-ID: <20041006004204.GA13074@shaka.acc.umu.se>
Permalink:  ../20041006004204.GA13074@shaka.acc.umu.se/
Also on:    postgresql.org/message-id/20041006004204.GA13074@shaka.acc.umu.se

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: crotalus@acc.umu.se
  Subject: Re: Planner picks the wrong plan?
  In-Reply-To: <20041006004204.GA13074@shaka.acc.umu.se>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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