From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christian Brink <cbrink@r-stream.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
Date: Mon, 22 Mar 2010 15:21:35 -0400
Message-ID: <12444.1269285695@sss.pgh.pa.us> (raw)
In-Reply-To: <4BA7C057.9090909@r-stream.com>
References: <4BA7C057.9090909@r-stream.com>
Christian Brink <cbrink@r-stream.com> writes:
> -> Nested Loop (cost=0.01..2416.59 rows=22477 width=4)
> (actual time=0.595..2.150 rows=225 loops=1)
> -> Index Scan using sysstrings_pkey on sysstrings
> (cost=0.00..8.27 rows=1 width=182) (actual time=0.110..0.112 rows=1 loops=1)
> Index Cond: (id = 'net/Console/Employee/Day End
> Time'::text)
> -> Index Scan using sales_tranzdate_index on sales s
> (cost=0.01..1846.40 rows=22477 width=12) (actual time=0.454..1.687
> rows=225 loops=1)
> Index Cond: ((s.tranzdate >= ('2010-02-15'::date +
> (sysstrings.data)::time without time zone)) AND (s.tranzdate <
> ('2010-02-16'::date + (sysstrings.data)::time without time zone)))
> Filter: ((NOT void) AND (NOT suspended))
The fundamental reason why you're getting a bad plan choice is the
factor-of-100 estimation error here. I'm not sure you can do a whole
lot about that without rethinking the query --- in particular I would
suggest trying to get rid of the non-constant range bounds. You're
apparently already plugging in an external variable for the date,
so maybe you could handle the time of day similarly instead of joining
to sysstrings for it.
regards, tom lane
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: tgl@sss.pgh.pa.us, cbrink@r-stream.com
Subject: Re: PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
In-Reply-To: <12444.1269285695@sss.pgh.pa.us>
* 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