pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query planner problem
4+ messages / 4 participants
[nested] [flat]

* Query planner problem
@ 2004-10-01 22:06 Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>
  2004-10-02 22:50 ` Re: Query planner problem Russell Smith <mr-russ@pws.com.au>
  2004-10-02 23:21 ` Re: Query planner problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Ryan VanMiddlesworth @ 2004-10-01 22:06 UTC (permalink / raw)
  To: pgsql-performance

Okay, I've got two queries that I think the planner should reduce to be 
logically equivalent but it's not happening. The example queries below have 
been simplified as much as possible while still producing the problem. 

What I'm trying to do is create a single prepared statement that can handle 
null parameters rather than have to dynamically generate the statement in my 
app code based on supplied parameters.

Basically the date constants below would be substituted with parameters 
supplied on a web search form (or nulls).


Here is the query and EXPLAIN that runs quickly:
  SELECT case_id FROM case_data 
  WHERE case_filed_date > '2004-09-16' 
    AND case_filed_date < '2004-09-20'

                       QUERY PLAN
-------------------------------------------------------------
Index Scan using case_data_case_filed_date on case_data  
(cost=0.00..13790.52 rows=3614 width=18)
  Index Cond: ((case_filed_date > '2004-09-16'::date) 
           AND (case_filed_date < '2004-09-20'::date))


And here is the query and EXPLAIN from the version that I believe the planner 
should reduce to be logically equivalent:
 SELECT case_id FROM case_data 
 WHERE (('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'))
   AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'))

                       QUERY PLAN
-------------------------------------------------------------
Seq Scan on case_data  (cost=0.00..107422.02 rows=27509 width=18)
  Filter: ((('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'::date))
       AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'::date)))


I was hoping that the null comparisons would get folded out by the planner 
relatively cheaply. But as you can see, the first query uses indexes and the 
second one uses sequence scans, thereby taking much longer.  I guess my 
question is - is there a better way to accomplish what I'm doing in SQL or am 
I going to have to dynamically generate the statement based on supplied 
parameters?

Thanks,
Ryan



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

* Re: Query planner problem
  2004-10-01 22:06 Query planner problem Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>
@ 2004-10-02 22:50 ` Russell Smith <mr-russ@pws.com.au>
  2004-10-03 21:56   ` Re: Query planner problem Greg Stark <gsstark@mit.edu>
  1 sibling, 1 reply; 4+ messages in thread

From: Russell Smith @ 2004-10-02 22:50 UTC (permalink / raw)
  To: Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>; +Cc: pgsql-performance

On Sat, 2 Oct 2004 08:06 am, Ryan VanMiddlesworth wrote:

[snip]
> 
> 
> Here is the query and EXPLAIN that runs quickly:
>   SELECT case_id FROM case_data 
>   WHERE case_filed_date > '2004-09-16' 
>     AND case_filed_date < '2004-09-20'
> 
>                        QUERY PLAN
> -------------------------------------------------------------
> Index Scan using case_data_case_filed_date on case_data  
> (cost=0.00..13790.52 rows=3614 width=18)
>   Index Cond: ((case_filed_date > '2004-09-16'::date) 
>            AND (case_filed_date < '2004-09-20'::date))
> 
> 
> And here is the query and EXPLAIN from the version that I believe the planner 
> should reduce to be logically equivalent:
>  SELECT case_id FROM case_data 
>  WHERE (('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'))
>    AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'))
> 
>                        QUERY PLAN
> -------------------------------------------------------------
> Seq Scan on case_data  (cost=0.00..107422.02 rows=27509 width=18)
>   Filter: ((('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'::date))
>        AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'::date)))
> 
> 
> I was hoping that the null comparisons would get folded out by the planner 
> relatively cheaply. But as you can see, the first query uses indexes and the 
> second one uses sequence scans, thereby taking much longer.  I guess my 
> question is - is there a better way to accomplish what I'm doing in SQL or am 
> I going to have to dynamically generate the statement based on supplied 
> parameters?
> 
The Index does not store NULL values, so you have to do a tables scan to find NULL values.
That means the second query cannot use an Index, even if it wanted to.

Regards

Russell Smith




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

* Re: Query planner problem
  2004-10-01 22:06 Query planner problem Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>
  2004-10-02 22:50 ` Re: Query planner problem Russell Smith <mr-russ@pws.com.au>
@ 2004-10-03 21:56   ` Greg Stark <gsstark@mit.edu>
  0 siblings, 0 replies; 4+ messages in thread

From: Greg Stark @ 2004-10-03 21:56 UTC (permalink / raw)
  To: Russell Smith <mr-russ@pws.com.au>; +Cc: Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>; pgsql-performance


Russell Smith <mr-russ@pws.com.au> writes:

> The Index does not store NULL values

This is false.

Though the fact that NULL values are indexed in postgres doesn't help with
this poster's actual problem.

-- 
greg




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

* Re: Query planner problem
  2004-10-01 22:06 Query planner problem Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>
@ 2004-10-02 23:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2004-10-02 23:21 UTC (permalink / raw)
  To: Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>; +Cc: pgsql-performance

Ryan VanMiddlesworth <ryan@vanmiddlesworth.org> writes:
> And here is the query and EXPLAIN from the version that I believe the planner
> should reduce to be logically equivalent:
>  SELECT case_id FROM case_data 
>  WHERE (('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'))
>    AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'))

> I was hoping that the null comparisons would get folded out by the planner 
> relatively cheaply.

You could teach eval_const_expressions about simplifying NullTest nodes
if you think it's important enough.

			regards, tom lane



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


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

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-01 22:06 Query planner problem Ryan VanMiddlesworth <ryan@vanmiddlesworth.org>
2004-10-02 22:50 ` Russell Smith <mr-russ@pws.com.au>
2004-10-03 21:56   ` Greg Stark <gsstark@mit.edu>
2004-10-02 23:21 ` 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