On Thu, Sep 09, 2004 at 04:09:05PM +0800, Jason Ngo wrote: > Did that. Results of the explain statements: > > explain select max(trandate) from possales; > > Aggregate (cost=52967.84..52967.84 rows=1 width=4) > -> Seq Scan on possales (cost=0.00..49684.27 rows=1313427 width=4) > > explain select trandate from possales order by trandate desc limit 1; > > Limit (cost=0.00..3.92 rows=1 width=4) > -> Index Scan Backward using possales_trandate on possales > (cost=0.00..5148972.33 rows=1313427 width=4) > > Anyway, I saw from the docs that it really behaves this way. I guess > we'll have to rewrite all those SQL statements.
Yes but it would also be nice to tweak the postgresql configuration. A similar table (t2) here -- with the same number of tuples (1,313,427) and an index on a date attribute (f2) -- yields better numbers. OTOH it could be just that I ran this test on a faster machine. explain analyze select f2 from t2 order by f2 desc limit 1; Limit (cost=0.00..0.02 rows=1 width=4) (actual time=0.03..0.03 rows=1 loops=1) -> Index Scan Backward using t2_f2_idx on t2 (cost=0.00..30196.95 rows=1313427 width=4) (actual time=0.02..0.03 rows=2 loops=1) Total runtime: 0.07 msec explain analyze select max(f2) from t2; Aggregate (cost=22856.84..22856.84 rows=1 width=4) (actual time=2817.62..2817.62 rows=1 loops=1) -> Seq Scan on t2 (cost=0.00..19573.27 rows=1313427 width=4) (actual time=0.06..1830.67 rows=1313427 loops=1) Total runtime: 2817.71 msec -- $_=q:; # SHERWIN # 70;72;69;6e;74;20; 27;4a;75;73;74;20; 61;6e;6f;74;68;65; 72;20;50;65;72;6c; 20;6e;6f;76;69;63; 65;27;:;;s=~?(..); ?=pack q$C$,hex$1; ;;;=egg;;;;eval;;; -- Philippine Linux Users' Group (PLUG) Mailing List [EMAIL PROTECTED] (#PLUG @ irc.free.net.ph) Official Website: http://plug.linux.org.ph Searchable Archives: http://marc.free.net.ph . To leave, go to http://lists.q-linux.com/mailman/listinfo/plug . Are you a Linux newbie? To join the newbie list, go to http://lists.q-linux.com/mailman/listinfo/ph-linux-newbie
