Here's the queries and explains... Granted, it's not a huge difference
here,
but both timings are absolutely consistent. Using max(), this runs
almost
15 queries/sec and "limit 1" runs at almost 40 queries/sec.
Is the differene in explain analyze expected behavior? (rows=168196 vs.
rows=1)
(The table is freshly analayzed)
select max(item_id) from receipt_items
Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.030..0.031
rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..0.04 rows=1 width=4) (actual time=0.023..0.024
rows=1 loops=1)
-> Index Scan Backward using receipt_items_pkey on receipt_items
(cost=0.00..6883.71 rows=168196 width=4) (actual time=0.020..0.020
rows=1 loops=1)
Filter: (item_id IS NOT NULL)
Total runtime: 0.067 ms
select item_id
from receipt_items
order by item_id desc
limit 1
Limit (cost=0.00..0.04 rows=1 width=4) (actual time=0.010..0.011 rows=1
loops=1)
-> Index Scan Backward using receipt_items_pkey on receipt_items
(cost=0.00..6883.71 rows=168196 width=4) (actual time=0.008..0.008
rows=1 loops=1)
Total runtime: 0.026 ms
A couple more similar examples from this table:
select max(create_date) from receipt_items
Result (cost=0.05..0.06 rows=1 width=0) (actual time=0.032..0.032
rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..0.05 rows=1 width=8) (actual time=0.025..0.026
rows=1 loops=1)
-> Index Scan Backward using test_idx_1 on receipt_items
(cost=0.00..7986.82 rows=168196 width=8) (actual time=0.022..0.022
rows=1 loops=1)
Filter: (create_date IS NOT NULL)
Total runtime: 0.069 ms
select create_date
from receipt_items
order by create_date desc
limit 1;
Limit (cost=0.00..0.05 rows=1 width=8) (actual time=0.011..0.012 rows=1
loops=1)
-> Index Scan Backward using test_idx_1 on receipt_items
(cost=0.00..7986.82 rows=168196 width=8) (actual time=0.009..0.009
rows=1 loops=1)
Total runtime: 0.027 ms
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, January 07, 2007 8:48 PM
To: Adam Rich
Cc: 'Craig A. James'; 'Guy Rouillier'; 'PostgreSQL Performance'
Subject: Re: [PERFORM] High update activity, PostgreSQL vs BigDBMS
"Adam Rich" <adam.r@sbcglobal.net> writes:
> I'm using 8.2 and using order by & limit is still faster than MAX()
> even though MAX() now seems to rewrite to an almost identical plan
> internally.
Care to quantify that? AFAICT any difference is within measurement
noise, at least for the case of separately-issued SQL commands.
> Count(*) still seems to use a full table scan rather than an index
scan.
Yup. Don't hold your breath for something different. Postgres has made
design choices that make certain cases fast and others slow, and
count(*) is one case that has come out on the short end of the stick.
If that's your most important measure of performance, then indeed you
should select a different database that's made different tradeoffs.
regards, tom lane
