Re: strange query runtime

Olivier Sirven <olivier(at)elma(dot)fr> writes:
> Limit (cost=0.00..9677.68 rows=20 width=4)
> -> Nested Loop (cost=0.00..61006657.19 rows=126077 width=4)
> -> Nested Loop (cost=0.00..59991538.61 rows=252145 width=12)
> -> Index Scan Backward using generals_topics_pkey on
> generals_topics gt (cost=0.00..615679.86 rows=14750423 width=8)
> -> Index Scan using ix_category_generals_id_general on
> category_generals cs (cost=0.00..4.01 rows=1 width=4)
> Index Cond: ("outer".id_general = cs.id_general)
> Filter: (id_category = 3)
> -> Index Scan using generals_id_topic_key on generals g
> (cost=0.00..4.01 rows=1 width=4)
> Index Cond: (g.id_general = "outer".id_general)
> Filter: media

> As you can see, every rows of generals_topics table is scanned

No, they aren't, because of the LIMIT. The estimate for a plan node is
the number of rows it would return *if scanned to completion* ... but
the LIMIT will terminate execution as soon as it's gotten back 20 rows.
Hence the LIMIT cost estimate is only 20/126077 of the estimated cost
for the full nestloop.

Your problem probably has something to do with irregular distribution of
the id_category values in the id_topic sort order, but it's hard to tell
with no more data than you've provided.

regards, tom lane