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
