X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0BA7A329E93 for ; Wed, 6 Oct 2004 01:42:20 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06983-03 for ; Wed, 6 Oct 2004 00:42:07 +0000 (GMT) Received: from khan.acc.umu.se (khan.acc.umu.se [130.239.18.139]) by svr1.postgresql.org (Postfix) with ESMTP id 5BB42329D41 for ; Wed, 6 Oct 2004 01:42:09 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by amavisd-new (Postfix) with ESMTP id 22D0DD223 for ; Wed, 6 Oct 2004 02:42:08 +0200 (MEST) Received: from shaka.acc.umu.se (shaka.acc.umu.se [130.239.18.148]) by khan.acc.umu.se (Postfix) with ESMTP id 79363D2E2 for ; Wed, 6 Oct 2004 02:42:05 +0200 (MEST) Received: by shaka.acc.umu.se (Postfix, from userid 23132) id 385C2F; Wed, 6 Oct 2004 02:42:05 +0200 (MEST) Date: Wed, 6 Oct 2004 02:42:04 +0200 From: Nichlas =?iso-8859-1?Q?L=F6fdahl?= To: pgsql-performance@postgresql.org Subject: Planner picks the wrong plan? Message-ID: <20041006004204.GA13074@shaka.acc.umu.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at acc.umu.se X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/49 X-Sequence-Number: 8525 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