Обсуждение: limit + order by is slow if no rows in result set
There are 1.9M rows in ts_defects and indexes on b.ts_id (primary key) d.ts_biz_event_id and d.ts_occur_date. Both queries below return 0 rows. The 1st runs fast and the 2nd > 400x slower. The 2nd query differs from the 1st only by the addition of "limit 1". Why the big difference in performance? Thanks, Brian [bcox@athena jsp]$ time PGPASSWORD=**** psql -U admin -d cemdb -h 192.168.1.30 -c 'select * from ts_defects d join ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order by d.ts_occur_date desc;' (column list deleted) -------+--------------+--------------+---------------+---------------------------+----------------+----------------+------------+------------------------+------------------+-----------------+---------------+------------------+---------------+-----------------+-------------------+---------------------+---------------------+---------------------+--------------------+----------------+--------------------+----------------------+--------------------+----------------------+-----------------------+----------------+----------------------+---------------+-----------------+------------------+--------------+----------------+-------+--------------+---------------+---------------------------+------------------+---------+--------------------+---------------+-----------------+------------------+---------------+----------------------+---------------------+--------------------+-----------+---------------------+----------+---------------+--------------+------------------+-------------+-------- -----+--------------+--------------+---------------- (0 rows) real 0m0.022s user 0m0.003s sys 0m0.003s [bcox@athena jsp]$ time PGPASSWORD=**** psql -U admin -d cemdb -h 192.168.1.30 -c 'select * from ts_defects d join ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order by d.ts_occur_date desc limit 1;' (column list deleted) -------+--------------+--------------+---------------+---------------------------+----------------+----------------+------------+------------------------+------------------+-----------------+---------------+------------------+---------------+-----------------+-------------------+---------------------+---------------------+---------------------+--------------------+----------------+--------------------+----------------------+--------------------+----------------------+-----------------------+----------------+----------------------+---------------+-----------------+------------------+--------------+----------------+-------+--------------+---------------+---------------------------+------------------+---------+--------------------+---------------+-----------------+------------------+---------------+----------------------+---------------------+--------------------+-----------+---------------------+----------+---------------+--------------+------------------+-------------+-------- -----+--------------+--------------+---------------- (0 rows) real 0m9.410s user 0m0.005s sys 0m0.002s
Brian Cox wrote: > > There are 1.9M rows in ts_defects and indexes on b.ts_id (primary key) > d.ts_biz_event_id and d.ts_occur_date. Both queries below return 0 > rows. The 1st runs fast and the 2nd > 400x slower. The 2nd query > differs from the 1st only by the addition of "limit 1". > > Why the big difference in performance? Please run EXPLAIN ANALYZE on both queries, and send back the results. Also, what indexes are there on the tables involved? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi Heikki,
Thanks for your response.
> Please run EXPLAIN ANALYZE on both queries, and send back the results.
[bcox@athena jsp]$ PGPASSWORD=quality psql -U admin -d cemdb -h
192.168.1.30 -c 'explain analyze select * from ts_defects d join
ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order
by d.ts_occur_date desc;'
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=160400.01..160646.91 rows=98762 width=2715) (actual
time=0.303..0.303 rows=0 loops=1)
Sort Key: d.ts_occur_date
-> Hash Join (cost=33.20..82567.14 rows=98762 width=2715) (actual
time=0.218..0.218 rows=0 loops=1)
Hash Cond: ("outer".ts_biz_event_id = "inner".ts_id)
-> Seq Scan on ts_defects d (cost=0.00..71882.88
rows=1932688 width=1545) (actual time=0.022..0.022 rows=1 loops=1)
-> Hash (cost=33.04..33.04 rows=65 width=1170) (actual
time=0.135..0.135 rows=0 loops=1)
-> Bitmap Heap Scan on ts_biz_events b
(cost=2.23..33.04 rows=65 width=1170) (actual time=0.132..0.132 rows=0
loops=1)
Recheck Cond: (ts_status = 3)
-> Bitmap Index Scan on ts_biz_events_statusindex
(cost=0.00..2.23 rows=65 width=0) (actual time=0.054..0.054 rows=61
loops=1)
Index Cond: (ts_status = 3)
Total runtime: 0.586 ms
(11 rows)
[bcox@athena jsp]$ PGPASSWORD=quality psql -U admin -d cemdb -h
192.168.1.30 -c 'explain analyze select * from ts_defects d join
ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order
by d.ts_occur_date desc limit 1;'
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..87.37 rows=1 width=2715) (actual
time=17999.482..17999.482 rows=0 loops=1)
-> Nested Loop (cost=0.00..8628543.77 rows=98762 width=2715)
(actual time=17999.476..17999.476 rows=0 loops=1)
-> Index Scan Backward using ts_defects_dateindex on
ts_defects d (cost=0.00..227675.97 rows=1932688 width=1545) (actual
time=0.047..3814.923 rows=1932303 loops=1)
-> Index Scan using ts_biz_events_pkey on ts_biz_events b
(cost=0.00..4.33 rows=1 width=1170) (actual time=0.005..0.005 rows=0
loops=1932303)
Index Cond: (b.ts_id = "outer".ts_biz_event_id)
Filter: (ts_status = 3)
Total runtime: 17999.751 ms
(7 rows)
> Also, what indexes are there on the tables involved?
I tried to mention the relevant indexes in my original posting, but
omitted one; here's a list of all indexes:
ts_defects: ts_id, ts_occur_date, ts_defect_def_id, ts_biz_event_id,
ts_trancomp_id, ts_transet_incarnation_id, ts_transet_id,
ts_tranunit_id, ts_user_incarnation_id, ts_user_id
ts_biz_events: ts_id, ts_defect_def_id, ts_status
Thanks,
Brian
Brian Cox <brian.cox@ca.com> writes:
>> Please run EXPLAIN ANALYZE on both queries, and send back the results.
> [ results... ]
The reason the hash plan is fairly fast is that the hash join code has a
special hack: if it reads the inner relation and finds it contains no
rows, it knows there can be no join result rows, so it can fall out
without reading the outer relation at all. This saves it from scanning
the large ts_defects table. (If you look close you'll see that it
actually reads just the first row from ts_defects; this is because the
inner relation isn't read until after we know the outer is nonempty,
so as to try to win for the other case of empty outer and nonempty
inner.)
The reason the nestloop/limit plan is not fast is that it has to scan
the inner relation (ts_biz_events) for each row of ts_defects, and there
are lots of them. Even though each inner scan is just a fast index
probe, it adds up.
The reason the planner goes for the nestloop/limit plan is that it's
expecting that about 5% (98762/1932688) of the ts_defects rows will
have a match in ts_biz_events, and so it figures it'll only have to
probe ts_biz_events about 20 times before producing an output row,
and the Limit only wants one row. So this looks a lot cheaper than
the hash plan --- especially since the latter is being costed without
any assumption that the zero-inner-rows situation applies.
The bottom line is that the plans are being chosen on "typical" rather
than corner-case assumptions, and zero matching rows is a corner case
that happens to work real well for the hash plan and not well at all for
the nestloop plan. I'm not sure what we can do about that without
making the performance worse for the case of not-quite-zero matching
rows.
You might be able to get a better result if you increased the statistics
target for ts_status --- it looks like the planner thinks there are many
more ts_status = 3 rows than there really are.
regards, tom lane