pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlimit + order by is slow if no rows in result set
4+ messages / 3 participants
[nested] [flat]
* limit + order by is slow if no rows in result set
@ 2007-02-12 21:36 Brian Cox <brian.cox@ca.com>
2007-02-12 23:26 ` Re: limit + order by is slow if no rows in result set Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Brian Cox @ 2007-02-12 21:36 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: limit + order by is slow if no rows in result set
2007-02-12 21:36 limit + order by is slow if no rows in result set Brian Cox <brian.cox@ca.com>
@ 2007-02-12 23:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 4+ messages in thread
From: Heikki Linnakangas @ 2007-02-12 23:26 UTC (permalink / raw)
To: Brian Cox <brian.cox@ca.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: limit + order by is slow if no rows in result set
@ 2007-02-13 00:24 Brian Cox <brian.cox@ca.com>
2007-02-13 03:36 ` Re: limit + order by is slow if no rows in result set Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Brian Cox @ 2007-02-13 00:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: limit + order by is slow if no rows in result set
2007-02-13 00:24 Re: limit + order by is slow if no rows in result set Brian Cox <brian.cox@ca.com>
@ 2007-02-13 03:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-02-13 03:36 UTC (permalink / raw)
To: Brian Cox <brian.cox@ca.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-02-13 03:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-12 21:36 limit + order by is slow if no rows in result set Brian Cox <brian.cox@ca.com>
2007-02-12 23:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-02-13 00:24 Re: limit + order by is slow if no rows in result set Brian Cox <brian.cox@ca.com>
2007-02-13 03:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox