Hi all!
Please, just look at these query explanations and try to explain why
planner does so (PostgreSQL 8.4).
There is an index on table sms (number, timestamp).

And three fast & simple queries:
=# explain analyze select max(timestamp) from sms where number='5502712';
-------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=3.79..3.80 rows=1 width=0) (actual time=0.269..0.270
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..3.79 rows=1 width=8) (actual
time=0.259..0.260 rows=1 loops=1)
-> Index Scan Backward using sms_number_timestamp on sms
(cost=0.00..5981.98 rows=1579 width=8) (actual time=0.253..0.253
rows=1 loops=1)
Index Cond: ((number)::text = '5502712'::text)
Filter: ("timestamp" IS NOT NULL)
Total runtime: 0.342 ms

=# explain analyze select max(timestamp) from sms where number='5802693';
-------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=3.79..3.80 rows=1 width=0) (actual time=0.425..0.426
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..3.79 rows=1 width=8) (actual
time=0.413..0.414 rows=1 loops=1)
-> Index Scan Backward using sms_number_timestamp on sms
(cost=0.00..5981.98 rows=1579 width=8) (actual time=0.409..0.409
rows=1 loops=1)
Index Cond: ((number)::text = '5802693'::text)
Filter: ("timestamp" IS NOT NULL)
Total runtime: 0.513 ms

=# explain analyze select max(timestamp) from sms where number='5802693';
-------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=3.79..3.80 rows=1 width=0) (actual time=0.425..0.426
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..3.79 rows=1 width=8) (actual
time=0.413..0.414 rows=1 loops=1)
-> Index Scan Backward using sms_number_timestamp on sms
(cost=0.00..5981.98 rows=1579 width=8) (actual time=0.409..0.409
rows=1 loops=1)
Index Cond: ((number)::text = '5802693'::text)
Filter: ("timestamp" IS NOT NULL)
Total runtime: 0.513 ms



But this does not work:
# explain analyze select max(timestamp) from sms where number in
('5502712','5802693','5801981');
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=15912.30..15912.31 rows=1 width=8) (actual
time=587.952..587.954 rows=1 loops=1)
-> Bitmap Heap Scan on sms (cost=1413.02..15758.71 rows=61432
width=8) (actual time=34.266..491.853 rows=59078 loops=1)
Recheck Cond: ((number)::text = ANY
('{5502712,5802693,5801981}'::text[]))
-> Bitmap Index Scan on sms_number_timestamp
(cost=0.00..1397.67 rows=61432 width=0) (actual time=30.778..30.778
rows=59078 loops=1)
Index Cond: ((number)::text = ANY
('{5502712,5802693,5801981}'::text[]))
Total runtime: 588.199 ms

And this too:
# explain analyze select max(timestamp) from sms where
number='5502712' or number='5802693' or number='5801981';
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16205.75..16205.76 rows=1 width=8) (actual
time=851.204..851.205 rows=1 loops=1)
-> Bitmap Heap Scan on sms (cost=1473.31..16052.17 rows=61432
width=8) (actual time=68.233..745.004 rows=59090 loops=1)
Recheck Cond: (((number)::text = '5502712'::text) OR
((number)::text = '5802693'::text) OR ((number)::text =
'5801981'::text))
-> BitmapOr (cost=1473.31..1473.31 rows=61592 width=0)
(actual time=64.992..64.992 rows=0 loops=1)
-> Bitmap Index Scan on sms_number_timestamp
(cost=0.00..40.27 rows=1579 width=0) (actual time=0.588..0.588 rows=59
loops=1)
Index Cond: ((number)::text = '5502712'::text)
-> Bitmap Index Scan on sms_number_timestamp
(cost=0.00..40.27 rows=1579 width=0) (actual time=0.266..0.266 rows=59
loops=1)
Index Cond: ((number)::text = '5802693'::text)
-> Bitmap Index Scan on sms_number_timestamp
(cost=0.00..1346.69 rows=58434 width=0) (actual time=64.129..64.129
rows=58972 loops=1)
Index Cond: ((number)::text = '5801981'::text)
Total runtime: 853.176 ms


According to planner cost estimations - it has enough data to
understand that it is better to aggregate maximum from three
subqueries. I suppose it's not a bug but not implemented feature -
maybe there is already something about it on roadmap?


--
Vladimir Kulev
Mobile: +7 (921) 555-44-22

Jabber: me@lightoze.net

Skype: lightoze

Search Discussions

  • Kevin Grittner at Jun 20, 2011 at 3:41 pm

    Vladimir Kulev wrote:

    # explain analyze select max(timestamp) from sms where number in
    ('5502712','5802693','5801981');
    According to planner cost estimations - it has enough data to
    understand that it is better to aggregate maximum from three
    subqueries. I suppose it's not a bug but not implemented feature
    Yeah, you're hoping for an optimization which hasn't been
    implemented.

    I expect you're hoping for a plan similar to what this gives you?:

    explain analyze select greatest(
    (select max(timestamp) from sms where number = '5502712'),
    (select max(timestamp) from sms where number = '5802693'),
    (select max(timestamp) from sms where number = '5801981'));

    -Kevin
  • Vladimir Kulev at Jun 20, 2011 at 4:13 pm
    Yes, exactly :)

    On Mon, Jun 20, 2011 at 7:41 PM, Kevin Grittner
    wrote:
    I expect you're hoping for a plan similar to what this gives you?:

    explain analyze select greatest(
    (select max(timestamp) from sms where number = '5502712'),
    (select max(timestamp) from sms where number = '5802693'),
    (select max(timestamp) from sms where number = '5801981'));
    --
    Vladimir Kulev
    Mobile: +7 (921) 555-44-22

    Jabber: me@lightoze.net

    Skype: lightoze
  • F. BROUARD / SQLpro at Jun 21, 2011 at 10:49 am
    Le 20/06/2011 18:08, Vladimir Kulev a écrit :
    Yes, exactly :)
    SQL Server does it but PG does not. Expect this for the future....

    So try to rewrite the query like this :

    select max(timestamp) from sms where number = '5502712'
    UNIUON ALL,
    select max(timestamp) from sms where number = '5802693'
    UNION ALL
    select max(timestamp) from sms where number = '5801981'

    To see what happen to the query plan !

    A +
    On Mon, Jun 20, 2011 at 7:41 PM, Kevin Grittner
    wrote:
    I expect you're hoping for a plan similar to what this gives you?:

    explain analyze select greatest(
    (select max(timestamp) from sms where number = '5502712'),
    (select max(timestamp) from sms where number = '5802693'),
    (select max(timestamp) from sms where number = '5801981'));

    --
    Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
    Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
    Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
    Audit, conseil, expertise, formation, modélisation, tuning, optimisation
    *********************** http://www.sqlspot.com *************************
  • Gaetano Mendola at Jul 15, 2011 at 7:43 am

    On 20/06/2011 07:35, Vladimir Kulev wrote:

    But this does not work:
    # explain analyze select max(timestamp) from sms where number in
    ('5502712','5802693','5801981');
    Try to rewrite that query this way:

    explain analyze select timestamp from sms where number in
    ('5502712','5802693','5801981') order by timestamp desc limit 1;


    Regards
    Gaetano Mendola
  • Gaetano Mendola at Jul 15, 2011 at 7:43 am

    On 20/06/2011 07:35, Vladimir Kulev wrote:

    But this does not work:
    # explain analyze select max(timestamp) from sms where number in
    ('5502712','5802693','5801981');
    Try to rewrite that query this way:

    explain analyze select timestamp from sms where number in
    ('5502712','5802693','5801981') order by timestamp desc limit 1;


    Regards
    Gaetano Mendola

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 20, '11 at 5:44a
activeJul 15, '11 at 7:43a
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase