pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Wrong plan or what ?
8+ messages / 3 participants
[nested] [flat]

* Wrong plan or what ?
@ 2003-07-22 17:10 Mendola Gaetano <mendola@bigfoot.com>
  2003-07-22 17:40 ` Re: [PERFORM] Wrong plan or what ? Josh Berkus <josh@agliodbs.com>
  2003-07-22 17:56 ` Re: Wrong plan or what ? Josh Berkus <josh@agliodbs.com>
  2003-07-26 10:53 ` Re: Wrong plan or what ? HansH <hartenhans@op.het.net>
  0 siblings, 3 replies; 8+ messages in thread

From: Mendola Gaetano @ 2003-07-22 17:10 UTC (permalink / raw)
  To: pgsql-performance; pgsql-admin@postgresql.org

Hi all,
I'm running Postgres7.3.3 and I'm performing this simple select:

select *
from user_logs ul,
     user_data ud,
     class_default cd
where
     ul.id_user = ud.id_user and
     ud.id_class = cd.id_class and
     cd.id_provider = 39;

these are the number of rows for each table:

user_logs:  1258955
class_default: 31       ( only one with id_provider = 39 )
user_data: 10274;


this is the explain analyze for that query:

QUERY PLAN
 Hash Join  (cost=265.64..32000.76 rows=40612 width=263) (actual
time=11074.21..11134.28 rows=10 loops=1)
   Hash Cond: ("outer".id_user = "inner".id_user)
   ->  Seq Scan on user_logs ul  (cost=0.00..24932.65 rows=1258965 width=48)
(actual time=0.02..8530.21 rows=1258966 loops=1)
   ->  Hash  (cost=264.81..264.81 rows=331 width=215) (actual
time=30.22..30.22 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..264.81 rows=331 width=215) (actual
time=29.95..30.20 rows=6 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.08..0.10 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..258.49 rows=395 width=160) (actual time=29.82..29.96 rows=6
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 11135.65 msec
(10 rows)


I'm able to  performe that select with these 3 steps:

SELECT id_class from class_default where id_provider = 39;
 id_class
----------
       48
(1 row)

SELECT id_user from user_data where id_class in ( 48 );
 id_user
---------
   10943
   10942
   10934
   10927
   10910
   10909
(6 rows)


SELECT * from user_logs where id_user in (
 10943,   10942,   10934,   10927,   10910,  10909
);
[SNIPPED]

and the time ammount is a couple of milliseconds.

Why the planner or the executor ( I don't know ) do not follow
the same strategy ?



Thank you
Gaetano Mendola











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

* Re: [PERFORM] Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
@ 2003-07-22 17:40 ` Josh Berkus <josh@agliodbs.com>
  2003-07-22 17:48   ` Re: [PERFORM] Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2003-07-22 17:56   ` Re: [PERFORM] Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2 siblings, 2 replies; 8+ messages in thread

From: Josh Berkus @ 2003-07-22 17:40 UTC (permalink / raw)
  To: Mendola Gaetano <mendola@bigfoot.com>; pgsql-performance; pgsql-admin@postgresql.org

Gaetano,

> SELECT * from user_logs where id_user in (
>  10943,   10942,   10934,   10927,   10910,  10909
> );
> [SNIPPED]

> Why the planner or the executor ( I don't know ) do not follow
> the same strategy ?

It is, actually, according to the query plan.   

Can you post the EXPLAIN ANALYZE for the above query?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: [PERFORM] Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2003-07-22 17:40 ` Re: [PERFORM] Wrong plan or what ? Josh Berkus <josh@agliodbs.com>
@ 2003-07-22 17:48   ` Mendola Gaetano <mendola@bigfoot.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Mendola Gaetano @ 2003-07-22 17:48 UTC (permalink / raw)
  To: josh@agliodbs.com; pgsql-performance; pgsql-admin@postgresql.org

"Josh Berkus" <josh@agliodbs.com>
> Gaetano,
>
> > SELECT * from user_logs where id_user in (
> >  10943,   10942,   10934,   10927,   10910,  10909
> > );
> > [SNIPPED]
>
> > Why the planner or the executor ( I don't know ) do not follow
> > the same strategy ?
>
> It is, actually, according to the query plan.
>
> Can you post the EXPLAIN ANALYZE for the above query?

Index Scan using idx_user_user_logs, idx_user_user_logs, idx_user_user_logs,
idx_user_user_logs, idx_user_user_logs, idx_user_user_logs on user_logs
(cost=0.00..5454.21 rows=2498 width=48) (actual time=0.09..0.28 rows=10
loops=1)
   Index Cond: ((id_user = 10943) OR (id_user = 10942) OR (id_user = 10934)
OR (id_user = 10927) OR (id_user = 10910) OR (id_user = 10909))
 Total runtime: 0.41 msec
(3 rows)


Thank you
Gaetano


PS: if I execute the query I obtain 10 rows instead of 3 that say the
explain analyze.






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

* Re: [PERFORM] Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2003-07-22 17:40 ` Re: [PERFORM] Wrong plan or what ? Josh Berkus <josh@agliodbs.com>
@ 2003-07-22 17:56   ` Mendola Gaetano <mendola@bigfoot.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Mendola Gaetano @ 2003-07-22 17:56 UTC (permalink / raw)
  To: josh@agliodbs.com; pgsql-performance; pgsql-admin@postgresql.org

Forget my PS to last message.




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

* Re: Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
@ 2003-07-22 17:56 ` Josh Berkus <josh@agliodbs.com>
  2003-07-22 18:35   ` Re: [PERFORM] Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2003-07-22 17:56 UTC (permalink / raw)
  To: Mendola Gaetano <mendola@bigfoot.com>; pgsql-performance

Gaetano,

> QUERY PLAN
>  Hash Join  (cost=265.64..32000.76 rows=40612 width=263) (actual
> time=11074.21..11134.28 rows=10 loops=1)
>    Hash Cond: ("outer".id_user = "inner".id_user)
>    ->  Seq Scan on user_logs ul  (cost=0.00..24932.65 rows=1258965 width=48)
> (actual time=0.02..8530.21 rows=1258966 loops=1)

OK, here's your problem

The planner thinks that you're going to get 40162 rows out of the final join, 
not 10.   If the row estimate was correct, then the Seq Scan would be a 
reasonable plan.   But it's not.   Here's some steps you can take to clear 
things up for the planner:

1) Make sure you've VACUUM ANALYZED
2) Adjust the following postgresql.conf statistics:
	a) effective_cache_size: increase to 70% of available (not used by other 
processes) RAM.
	b) random_page_cost: decrease, maybe to 2.
	c) default_statistics_target: try increasing to 100
		(warning: this will significantly increase the time required to do ANALYZE)

Then test again!


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: [PERFORM] Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2003-07-22 17:56 ` Re: Wrong plan or what ? Josh Berkus <josh@agliodbs.com>
@ 2003-07-22 18:35   ` Mendola Gaetano <mendola@bigfoot.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Mendola Gaetano @ 2003-07-22 18:35 UTC (permalink / raw)
  To: josh@agliodbs.com; pgsql-performance; +Cc: pgsql-admin@postgresql.org

"Josh Berkus" <josh@agliodbs.com>
> Gaetano,
>
> > QUERY PLAN
> >  Hash Join  (cost=265.64..32000.76 rows=40612 width=263) (actual
> > time=11074.21..11134.28 rows=10 loops=1)
> >    Hash Cond: ("outer".id_user = "inner".id_user)
> >    ->  Seq Scan on user_logs ul  (cost=0.00..24932.65 rows=1258965
width=48)
> > (actual time=0.02..8530.21 rows=1258966 loops=1)
>
> OK, here's your problem
>
> The planner thinks that you're going to get 40162 rows out of the final
join,
> not 10.   If the row estimate was correct, then the Seq Scan would be a
> reasonable plan.   But it's not.   Here's some steps you can take to clear
> things up for the planner:
>
> 1) Make sure you've VACUUM ANALYZED
> 2) Adjust the following postgresql.conf statistics:
> a) effective_cache_size: increase to 70% of available (not used by other
> processes) RAM.
> b) random_page_cost: decrease, maybe to 2.
> c) default_statistics_target: try increasing to 100
> (warning: this will significantly increase the time required to do
ANALYZE)
>
> Then test again!

No improvement at all,
I pushed default_statistics_target to 1000
but the rows expected are still 40612 :-(
Of course I restarted the postmaster and I vacuumed analyze the DB


Thank you
Gaetano










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

* Re: Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
@ 2003-07-26 10:53 ` HansH <hartenhans@op.het.net>
  2003-08-01 15:49   ` Re: Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2 siblings, 1 reply; 8+ messages in thread

From: HansH @ 2003-07-26 10:53 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

In response to "Mendola Gaetano":
> I'm running Postgres7.3.3 and I'm performing this simple select:

Looking at your fast three step plan
> SELECT id_class from class_default where id_provider = 39;
> SELECT id_user from user_data where id_class in ( 48 );
> SELECT * from user_logs where id_user in (
>  10943,   10942,   10934,   10927,   10910,  10909 );
I'ld stem for reordering the from and where clauses alike:
    select *
    from
           class_default cd,
           user_data ud,
           user_logs ul
    where
           cd.id_provider = 39 and
           ud.id_class = cd.id_class and
           ul.id_user = ud.id_user;

Personally I dislike implied joins and rather go for _about_ this:
    select *
    from
           ( class_default cd
           LEFT JOIN user_data ud ON ud.id_class = cd.id_class )
           LEFT JOIN user_logs ul  ON ul.id_user = ud.id_user,
    where
           cd.id_provider = 39;

Good luck,

HansH








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

* Re: Wrong plan or what ?
  2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
  2003-07-26 10:53 ` Re: Wrong plan or what ? HansH <hartenhans@op.het.net>
@ 2003-08-01 15:49   ` Mendola Gaetano <mendola@bigfoot.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Mendola Gaetano @ 2003-08-01 15:49 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org; +Cc: \"HansH\" <hartenhans@op.het.net>

""HansH"" <hartenhans@op.het.net>
> In response to "Mendola Gaetano":
> > I'm running Postgres7.3.3 and I'm performing this simple select:
>
> Looking at your fast three step plan
> > SELECT id_class from class_default where id_provider = 39;
> > SELECT id_user from user_data where id_class in ( 48 );
> > SELECT * from user_logs where id_user in (
> >  10943,   10942,   10934,   10927,   10910,  10909 );
> I'ld stem for reordering the from and where clauses alike:
>     select *
>     from
>            class_default cd,
>            user_data ud,
>            user_logs ul
>     where
>            cd.id_provider = 39 and
>            ud.id_class = cd.id_class and
>            ul.id_user = ud.id_user;


still wrong:

Hash Join  (cost=267.10..32994.34 rows=41881 width=264) (actual
time=6620.17..6847.20 rows=94 loops=1)
   Hash Cond: ("outer".id_user = "inner".id_user)
   ->  Seq Scan on user_logs ul  (cost=0.00..25712.15 rows=1298315 width=48)
(actual time=0.01..5381.69 rows=1298351 loops=1)
   ->  Hash  (cost=266.25..266.25 rows=339 width=216) (actual
time=0.89..0.89 rows=0 loops=1)
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.16..0.83 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.08..0.09 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.06..0.40 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 6847.60 msec
(10 rows)


the returned are 94.


> Personally I dislike implied joins and rather go for _about_ this:
>     select *
>     from
>            ( class_default cd
>            LEFT JOIN user_data ud ON ud.id_class = cd.id_class )
>            LEFT JOIN user_logs ul  ON ul.id_user = ud.id_user,
>     where
>            cd.id_provider = 39;

worst:

 Merge Join  (cost=280.48..55717.14 rows=41881 width=264) (actual
time=18113.64..18182.94 rows=105 loops=1)
   Merge Cond: ("outer".id_user = "inner".id_user)
   ->  Index Scan using idx_user_user_logs on user_logs ul
(cost=0.00..51665.66 rows=1298315 width=48) (actual time=10.78..15459.37
rows=1298354 loops=1)
   ->  Sort  (cost=280.48..281.33 rows=339 width=216) (actual
time=1.11..1.20 rows=105 loops=1)
         Sort Key: ud.id_user
         ->  Nested Loop  (cost=0.00..266.25 rows=339 width=216) (actual
time=0.14..0.82 rows=21 loops=1)
               ->  Seq Scan on class_default cd  (cost=0.00..1.39 rows=1
width=55) (actual time=0.07..0.07 rows=1 loops=1)
                     Filter: (id_provider = 39)
               ->  Index Scan using idx_user_data_class on user_data ud
(cost=0.00..260.00 rows=389 width=161) (actual time=0.05..0.39 rows=21
loops=1)
                     Index Cond: (ud.id_class = "outer".id_class)
 Total runtime: 18185.61 msec

:-(



thank you anyway.

Gaetano





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


end of thread, other threads:[~2003-08-01 15:49 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-22 17:10 Wrong plan or what ? Mendola Gaetano <mendola@bigfoot.com>
2003-07-22 17:40 ` Josh Berkus <josh@agliodbs.com>
2003-07-22 17:48   ` Mendola Gaetano <mendola@bigfoot.com>
2003-07-22 17:56   ` Mendola Gaetano <mendola@bigfoot.com>
2003-07-22 17:56 ` Josh Berkus <josh@agliodbs.com>
2003-07-22 18:35   ` Mendola Gaetano <mendola@bigfoot.com>
2003-07-26 10:53 ` HansH <hartenhans@op.het.net>
2003-08-01 15:49   ` Mendola Gaetano <mendola@bigfoot.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