Query plan for "heavy" SELECT with "lite" sub-SELECTs - Mailing list pgsql-performance

From Nikolay Samokhvalov
Subject Query plan for "heavy" SELECT with "lite" sub-SELECTs
Date
Msg-id e431ff4c0611020307x1fca3a14w16f60dbd80d0e856@mail.gmail.com
Whole thread Raw
Responses Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs
Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs
List pgsql-performance
Hello,

I do not understand, why Postgres very ofter starts execution from
sub-select instead of doing main select and then proceeding to "lite"
sub-selects. For example:

(example is quite weird, but it demonstrates the problem)

1. explain analyze select * from pg_proc offset 1500 limit 1;
"Limit  (cost=116.91..116.99 rows=1 width=365) (actual
time=2.111..2.112 rows=1 loops=1)"
"  ->  Seq Scan on pg_proc  (cost=0.00..175.52 rows=2252 width=365)
(actual time=0.034..1.490 rows=1501 loops=1)"
"Total runtime: 2.156 ms"

3. explain analyze select oid,* from pg_type where oid=2277 limit 1;
"Limit  (cost=0.00..5.91 rows=1 width=816) (actual time=0.021..0.022
rows=1 loops=1)"
"  ->  Index Scan using pg_type_oid_index on pg_type  (cost=0.00..5.91
rows=1 width=816) (actual time=0.018..0.018 rows=1 loops=1)"
"        Index Cond: (oid = 2277::oid)"
"Total runtime: 0.079 ms"

2. explain analyze select
  *,
  (select typname from pg_type where pg_type.oid=pg_proc.prorettype limit 1)
from pg_proc offset 1500 limit 1;
"Limit  (cost=8983.31..8989.30 rows=1 width=365) (actual
time=17.648..17.649 rows=1 loops=1)"
"  ->  Seq Scan on pg_proc  (cost=0.00..13486.95 rows=2252 width=365)
(actual time=0.100..16.851 rows=1501 loops=1)"
"        SubPlan"
"          ->  Limit  (cost=0.00..5.91 rows=1 width=64) (actual
time=0.006..0.007 rows=1 loops=1501)"
"                ->  Index Scan using pg_type_oid_index on pg_type
(cost=0.00..5.91 rows=1 width=64) (actual time=0.004..0.004 rows=1
loops=1501)"
"                      Index Cond: (oid = $0)"
"Total runtime: 17.784 ms"

We see that in the 2nd example Postgres starts with "Index Scan using
pg_type_oid_index" (1501 iterations!). My understanding of SQL says me
that the simplest (and, in this case - and probably in *most* cases -
fastest) way to perform such queries is to start from main SELECT and
then, when we already have rows from "main" table, perform "lite"
sub-selects. So, I expected  smth near 2.156 ms + 0.079 ms, but obtain
17.784 ms... For large table this is killing behaviour.

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)?

--
Best regards,
Nikolay

pgsql-performance by date:

Previous
From: "Simon Riggs"
Date:
Subject: Re: Help w/speeding up range queries?
Next
From: Richard Huxton
Date:
Subject: Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs