Hi. I've got a query that runs fine (~50ms). When I add a "LIMIT 25" to it though, it takes way longer. The query itself then takes about 4.5 seconds. And when I do an explain, it takes 90+ seconds for the same query!
Explains and detailed table/view info below. tbl_log has 1.2M records, tbl_reference has 550k. This is 9.6.19 on CentOS 6 with PDGG packages.
CentOS6 has slow clock calls, so it is not surprising that EXPLAIN ANALYZE with TIMING defaulting to ON is slow. Using something more modern for the distribution should really help that, but for the current case just setting TIMING OFF should be good enough as it is the row counts which are interesting, not the timing of individual steps.
I know the query itself could be re-written, but it's coming from an ORM, so I'm really focused on why the adding a limit is causing such performance degradation, and what to do about it.
But if it is coming from an ORM and you can't rewrite it, then what can you do about it? Can you set enable_someting or something_cost parameters locally just for the duration of one query? If the ORM doesn't let you re-write, then I doubt it would let you do that, either. Since you are using such an old version, you can't create multivariate statistics, either (although I doubt they would help anyway).
-> Nested Loop (cost=4313.36..14216.18 rows=611586 width=336) (actual time=10.837..38.177 rows=1432 loops=1)
-> HashAggregate (cost=4312.93..4325.68 rows=1275 width=136) (actual time=10.802..13.800 rows=1433 loops=1)
-> Index Scan using tbl_log_pkey on public.tbl_log (cost=0.43..7.75 rows=1 width=336) (actual time=0.007..0.009 rows=1 loops=1433)
The way-off row estimate for the nested loop is the cause of the bad plan choice once you add the LIMIT. But what is the cause of the bad estimate? If you just multiply the estimates for each of the child nodes, you get about the correct answer. But the estimate for the nested loop is very different from the product of the children. On the one hand that isn't surprising, as the row estimates are computed at each node from first principles, not computed from the bottom up. But usually if the stats are way off, you can follow the error down to a lower level where they are also way off, but in this case you can't. That makes it really hard to reason about what the problem might be.
Can you clone your server, upgrade the clone to 12.4 or 13BETA3 or 14dev, and see if the problem still exists there? Can you anonymize your data so that you can publish an example other people could run themselves to dissect the problem; or maybe give some queries that generate random data which have the correct data distribution to reproduce the issue?
Cheers,
Jeff