pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sunquery and estimated rows
7+ messages / 4 participants
[nested] [flat]

* sunquery and estimated rows
@ 2004-04-16 21:45 Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Litao Wu @ 2004-04-16 21:45 UTC (permalink / raw)
  To: pgsql-performance

Hi,

When I included a subquery, the estimated rows (1240)
is way too high as shown in the following example. 
Can someone explain why? Because of this behavior,
some of our queries use hash join instead of nested
loop.

Thanks,

select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by
GCC 2.96
(1 row)
\d test
              Table "public.test"
 Column  |           Type           | Modifiers
---------+--------------------------+-----------
 id      | integer                  |
 name    | character varying(255)   |
 d_id    | integer                  |
 c_id    | integer                  |
 r_id    | integer                  |
 u_id    | integer                  |
 scope   | integer                  |
 active  | integer                  |
 created | timestamp with time zone |
 typ     | integer                  |
Indexes: test_scope_idx btree (scope)

reindex table test;
vacuum full analyze test;

select count(*) from test;
 count
-------
  4959
(1 row)
select count(*) from test where scope=10;
 count
-------
    10
(1 row)

explain analyze
select * from test
where scope=10; -- so far so good, estimate 12 rows,
actual 10 rows
                                                     
QUERY PLAN                                         
----------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test 
(cost=0.00..4.35 rows=12 width=59) (actual
time=0.04..0.11 rows=10 loops=1)
   Index Cond: (scope = 10)
 Total runtime: 0.23 msec
(3 rows)

explain analyze
select * from test
where scope=(select 10); -- estimate rows is way too
high, do not why????
                                                      
QUERY PLAN                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test 
(cost=0.00..40.74 rows=1240 width=59) (actual
time=0.06..0.13 rows=10 loops=1)
   Index Cond: (scope = $0)
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
(actual time=0.01..0.01 rows=1 loops=1)
 Total runtime: 0.22 msec
(5 rows)




	
		
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html



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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
@ 2004-04-16 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 19:22   ` Re: sunquery and estimated rows Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2004-04-16 23:45 UTC (permalink / raw)
  To: Litao Wu <litaowu@yahoo.com>; +Cc: pgsql-performance

Litao Wu <litaowu@yahoo.com> writes:
> When I included a subquery, the estimated rows (1240)
> is way too high as shown in the following example. 

> select * from test
> where scope=(select 10);

The planner sees that as "where scope = <some complicated expression>"
and falls back to a default estimate.  It won't simplify a sub-select
to a constant.  (Some people consider that a feature ;-).)

The estimate should still be derived from the statistics for the
scope column, but it will just depend on the number of distinct
values for the column and not on the specific comparison constant.

			regards, tom lane



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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-18 19:22   ` Markus Bertheau <twanger@bluetwanger.de>
  2004-04-18 23:09     ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-19 16:26     ` Re: sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Markus Bertheau @ 2004-04-18 19:22 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Litao Wu <litaowu@yahoo.com>; pgsql-performance

В Сбт, 17.04.2004, в 01:45, Tom Lane пишет:

> The planner sees that as "where scope = <some complicated expression>"
> and falls back to a default estimate.  It won't simplify a sub-select
> to a constant.  (Some people consider that a feature ;-).)

Why?

Thanks

-- 
Markus Bertheau <twanger@bluetwanger.de>




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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 19:22   ` Re: sunquery and estimated rows Markus Bertheau <twanger@bluetwanger.de>
@ 2004-04-18 23:09     ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 23:42       ` Re: sunquery and estimated rows Rod Taylor <rbt@rbt.ca>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2004-04-18 23:09 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Litao Wu <litaowu@yahoo.com>; pgsql-performance

Markus Bertheau <twanger@bluetwanger.de> writes:
> В Сбт, 17.04.2004, в 01:45, Tom Lane пишет:
>> The planner sees that as "where scope = <some complicated expression>"
>> and falls back to a default estimate.  It won't simplify a sub-select
>> to a constant.  (Some people consider that a feature ;-).)

> Why?

It's the only way to prevent it from simplifying when you don't want it
to.

			regards, tom lane



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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 19:22   ` Re: sunquery and estimated rows Markus Bertheau <twanger@bluetwanger.de>
  2004-04-18 23:09     ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-18 23:42       ` Rod Taylor <rbt@rbt.ca>
  2004-04-19 02:16         ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Rod Taylor @ 2004-04-18 23:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Litao Wu <litaowu@yahoo.com>; pgsql-performance

On Sun, 2004-04-18 at 19:09, Tom Lane wrote:
> Markus Bertheau <twanger@bluetwanger.de> writes:
> >  , 17.04.2004,  01:45, Tom Lane :
> >> The planner sees that as "where scope = <some complicated expression>"
> >> and falls back to a default estimate.  It won't simplify a sub-select
> >> to a constant.  (Some people consider that a feature ;-).)
> 
> > Why?
> 
> It's the only way to prevent it from simplifying when you don't want it
> to.

I'm having a difficult time coming up with a circumstance where that is
beneficial except when stats are out of whack.

Doesn't a prepared statement also falls back to the default estimate for
variables.

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 19:22   ` Re: sunquery and estimated rows Markus Bertheau <twanger@bluetwanger.de>
  2004-04-18 23:09     ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 23:42       ` Re: sunquery and estimated rows Rod Taylor <rbt@rbt.ca>
@ 2004-04-19 02:16         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2004-04-19 02:16 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Litao Wu <litaowu@yahoo.com>; pgsql-performance

Rod Taylor <rbt@rbt.ca> writes:
>> It's the only way to prevent it from simplifying when you don't want it
>> to.

> I'm having a difficult time coming up with a circumstance where that is
> beneficial except when stats are out of whack.

Try trawling the archives --- I recall several cases in which people
were using sub-selects for this purpose.

In any case, I don't see the value of having the planner check to see if
a sub-select is just a trivial arithmetic expression.  The cases where
people write that and expect it to be simplified are so few and far
between that I can't believe it'd be a good use of planner cycles.

			regards, tom lane



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

* Re: sunquery and estimated rows
  2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
  2004-04-16 23:45 ` Re: sunquery and estimated rows Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-18 19:22   ` Re: sunquery and estimated rows Markus Bertheau <twanger@bluetwanger.de>
@ 2004-04-19 16:26     ` Litao Wu <litaowu@yahoo.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Litao Wu @ 2004-04-19 16:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Well, the example shown is simplified version.
Now, let's see a little 'real' example (still
simplified version):

Table test is same as before:
\d test
              Table "public.test"
 Column  |           Type           | Modifiers
---------+--------------------------+-----------
 id      | integer                  |
 ...
 scope   | integer                  |
 ...      
Indexes: test_scope_idx btree (scope)

select count(*) from test;
 count
-------
  4959
(1 row)
select count(*) from test where scope=10;
 count
-------
    10
(1 row)

create table scope_def (scope int primary key, name
varchar(30) unique);
insert into scope_def values (10, 'TEST_SCOPE');

-- This is not a trivial arithmetic expression
explain analyze
select * from test
where scope=(select scope from scope_def where name =
'TEST_SCOPE');

-- estimated row is 1653, returned rows is 10
                                                      
                                                      
     QUERY PLAN                                       
          
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test 
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.08..0.15 rows=10 loops=1)
   Index Cond: (scope = $0)
   InitPlan
     ->  Index Scan using scope_def_name_key on
scope_def  (cost=0.00..4.82 rows=1 width=4) (actual
time=0.04..0.04 rows=1 loops=1)
           Index Cond: (name = 'TEST_SCOPE'::character
varying)
 Total runtime: 0.22 msec
(6 rows)


-- trivial arithmetic expression
-- estimated row is 1653, returned rows is 10
explain analyze
select * from test
where scope=(select 10);
                                                      
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test 
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.06..0.14 rows=10 loops=1)
   Index Cond: (scope = $0)
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
(actual time=0.01..0.01 rows=1 loops=1)
 Total runtime: 0.20 msec
(5 rows)

-- This is the plan I expect to see: estimated rows is
-- close the actual returned rows.
-- Do I have to devide the sub-select into two 
-- queries? 

explain analyze
select * from test
where scope=10;
                                                     
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Scan using test_scope_idx on test 
(cost=0.00..3.77 rows=10 width=59) (actual
time=0.05..0.12 rows=10 loops=1)
   Index Cond: (scope = 10)
 Total runtime: 0.18 msec
(3 rows)

-- Rewritten query using join in this case
explain analyze
select test.* from test JOIN scope_def using (scope)
where scope_def.name = 'TEST_SCOPE';
                                                      
     QUERY PLAN                                       
           
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..75.39 rows=5 width=63)
(actual time=0.07..0.19 rows=10 loops=1)
   ->  Index Scan using scope_def_name_key on
scope_def  (cost=0.00..4.82 rows=1 width=4) (actual
time=0.04..0.04 rows=1 loops=1)
         Index Cond: (name = 'TEST_SCOPE'::character
varying)
   ->  Index Scan using test_scope_idx on test 
(cost=0.00..49.91 rows=1653 width=59) (actual
time=0.02..0.09 rows=10 loops=1)
         Index Cond: (test.scope = "outer".scope)
 Total runtime: 0.28 msec
(6 rows)



	
		
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html



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


end of thread, other threads:[~2004-04-19 16:26 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-16 21:45 sunquery and estimated rows Litao Wu <litaowu@yahoo.com>
2004-04-16 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-18 19:22   ` Markus Bertheau <twanger@bluetwanger.de>
2004-04-18 23:09     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-18 23:42       ` Rod Taylor <rbt@rbt.ca>
2004-04-19 02:16         ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-19 16:26     ` Litao Wu <litaowu@yahoo.com>

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