From: Dave Dutcher <dave@tridecap.com>
To: nikolay@samokhvalov.com
To: pgsql-performance@postgresql.org
Subject: Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs
Date: Thu, 2 Nov 2006 08:25:27 -0600
Message-ID: <012a01c6fe8a$bee330e0$8300a8c0@tridecap.com> (raw)
In-Reply-To: <e431ff4c0611020307x1fca3a14w16f60dbd80d0e856@mail.gmail.com>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> Nikolay Samokhvalov
>
> What should I do to make Postgres work properly in such cases (I have
> a lot of similar queries; surely, they are executed w/o seqscans, but
> overall picture is the same - I see that starting from sub-selects
> dramatically decrease performance)?
How about this:
explain analyze
select (select typname from pg_type where pg_type.oid=mainq.prorettype limit
1)
from (select * from pg_proc offset 1500 limit 1) mainq;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------
Subquery Scan mainq (cost=50.99..56.85 rows=1 width=4) (actual
time=13.646..13.659 rows=1 loops=1)
-> Limit (cost=50.99..51.02 rows=1 width=310) (actual
time=13.575..13.579 rows=1 loops=1)
-> Seq Scan on pg_proc (cost=0.00..62.34 rows=1834 width=310)
(actual time=0.014..7.297 rows=1501 loops=1)
SubPlan
-> Limit (cost=0.00..5.82 rows=1 width=64) (actual time=0.038..0.043
rows=1 loops=1)
-> Index Scan using pg_type_oid_index on pg_type
(cost=0.00..5.82 rows=1 width=64) (actual time=0.028..0.028 rows=1 loops=1)
Index Cond: (oid = $0)
Total runtime: 13.785 ms
I would expect you to get closer to 2 ms on that query. My machine takes 13
ms to do just the seq scan of pg_proc.
Dave
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: dave@tridecap.com, nikolay@samokhvalov.com
Subject: Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs
In-Reply-To: <012a01c6fe8a$bee330e0$8300a8c0@tridecap.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