Андрей Репко wrote:
Здравствуйте Richard,
Tuesday, September 27, 2005, 2:08:31 PM, Вы писали:
sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=38565.30..38565.62 rows=32 width=4)
(actual time=15990.863..15990.933 rows=32 loops=1)
-> Seq Scan on ma_data (cost=0.00..37811.24 rows=301624
width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
Total runtime: 15991.244 ms
RH> OK - the planner thinks it's doing the right thing, your cost estimates
RH> are way off. If you look back at where you got an index-scan, it's cost
RH> was 1.1 million.
RH> Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57
But why PG scan _all_ the records in the table? As I understand we can
"just" select information from index, not scaning all the table? Of
course if we select ALL records from table index can't help us.
Actually, if you select more than 5-10% of the rows (in general) you are
better off using a seq-scan.
PostgreSQL estimates the total cost of possible query plans and picks
the cheapest. In your case your configuration settings seem to be
pushing the cost of an index scan much higher than it is. So, it picks
the sequential-scan.
If I write something like:
SELECT (SELECT alias_id FROM ma_data WHERE alias_id =1 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =2 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =3 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =4 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =5 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =6 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =7 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =8 LIMIT 1)
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =9 LIMIT 1)
...
UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id=max_alias_id
LIMIT 1)
It works better, much better.
Of course - it will always choose index queries here - it can see you
are only fetching one row in each subquery.
Correct your configuration settings so PG estimates the cost of an index
query correctly and all should be well.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
