On Tue, Aug 6, 2013 at 3:46 AM, Ivan Voras <ivoras@xxxxxxxxxxx> wrote: > Here are two more unexpected results. Same test table (1 mil. records, > "id" is SERIAL PRIMARY KEY, PostgreSQL 9.1, VACUUM ANALYZE performed > before the experiments): > > ivoras=# explain analyze select * from lt where id > 900000 limit 10; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------- > Limit (cost=0.00..1.71 rows=10 width=9) (actual > time=142.669..142.680 rows=10 loops=1) > -> Seq Scan on lt (cost=0.00..17402.00 rows=101630 width=9) > (actual time=142.665..142.672 rows=10 loops=1) > Filter: (id > 900000) > Total runtime: 142.735 ms > (4 rows) [skipped] > ivoras=# set enable_seqscan to off; > SET > ivoras=# explain analyze select * from lt where id > 900000 limit 10; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------- > Limit (cost=0.00..1.74 rows=10 width=9) (actual time=0.081..0.112 > rows=10 loops=1) > -> Index Scan using lt_pkey on lt (cost=0.00..17644.17 > rows=101630 width=9) (actual time=0.078..0.100 rows=10 loops=1) > Index Cond: (id > 900000) > Total runtime: 0.175 ms > (4 rows) > > It looks like the problem is in the difference between what the > planner expects and what the Filter or Index operations deliver: > (cost=0.00..17402.00 rows=101630 width=9) (actual > time=142.665..142.672 rows=10 loops=1). This might be caused by not accurate random_page_cost setting. This parameter gives planner a hint of how much it would cost to perform a random page read used by index scans. It looks like you need to decrease random_page_cost. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
