From: Richard Huxton <dev@archonet.com>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: bad plan
Date: Tue, 08 Mar 2005 19:20:22 +0000
Message-ID: <422DFAF6.2080201@archonet.com> (raw)
In-Reply-To: <422DF073.7080307@bigfoot.com>
References: <422D7F23.7000109@bigfoot.com>
<422D8F0E.8000906@archonet.com>
<422D9904.5010904@bigfoot.com>
<422DA6A5.2060908@archonet.com>
<422DF073.7080307@bigfoot.com>
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.075 rows=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.685 rows=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
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: dev@archonet.com, mendola@bigfoot.com
Subject: Re: bad plan
In-Reply-To: <422DFAF6.2080201@archonet.com>
* 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