Re: bad plan

Gaetano Mendola wrote:
> Richard Huxton wrote:
>
>
>>OK, so looking at the original EXPLAIN the order of processing seems to be:
>>1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
>>This gives us 31 rows
>>2. The left-join from v_sat_request to v_sc_packages is processed (lines
>>5..6)
>>This involves the subquery scan on vsp (from line 16) where it seems to
>>think the best idea is a merge join of programs to sequences.
>
>
> Whel basically v_sc_packages depends on other 3 views that are just a simple
> interface to a plain table.
>
>
> If I execute a select only on this table I get reasonable executions time:
>
>
> === cpu_tuple_cost = 0.07
>
> # explain analyze select * from v_sc_packages where id_package = 19628;
>                                                                               QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..15.96 rows=1 width=131) (actual time=41.450..41.494 rows=1 loops=1)
>    ->  Nested Loop  (cost=0.00..11.86 rows=1 width=116) (actual time=1.022..1.055 rows=1 loops=1)
>          ->  Nested Loop Left Join  (cost=0.00..7.89 rows=1 width=104) (actual time=0.330..0.345 rows=1 loops=1)
>                ->  Index Scan using packages_pkey on packages p  (cost=0.00..3.90 rows=1 width=104) (actual
time=0.070..0.075rows=1 loops=1) 
>                      Index Cond: (id_package = 19628)
>                ->  Index Scan using package_security_id_package_key on package_security ps  (cost=0.00..3.91 rows=1
width=4)(actual time=0.232..0.237 rows=1 loops=1) 
>                      Index Cond: ("outer".id_package = ps.id_package)
>          ->  Index Scan using idx_sequences_id_package on sequences  (cost=0.00..3.90 rows=1 width=16) (actual
time=0.670..0.685rows=1 loops=1) 
>                Index Cond: (19628 = id_package)
>                Filter: (estimated_start IS NOT NULL)
>    ->  Index Scan using programs_pkey on programs  (cost=0.00..4.02 rows=1 width=19) (actual time=0.078..0.086 rows=1
loops=1)
>          Index Cond: (programs.id_program = "outer".id_program)
>          Filter: (id_program <> 0)
>  Total runtime: 42.650 ms
> (14 rows)

> === cpu_tuple_cost = 0.01

> === cpu_tuple_cost = 0.001

I don't know what you think you're measuring, but it's nothing to do
with the plans. If you look at the plans carefully, you'll see they're
all the same. The "cost" numbers change because that's the parameter
you're changing.

I'm not sure it makes sense to vary cpu_tuple_cost from 0.07 down to
0.001 - that's a factor of 70 difference. I might be tempted to halve or
double it, but even then only after some serious testing.

--
   Richard Huxton
   Archonet Ltd