can't understand this policy:
dba400=# SELECT count(*) from annuncio400 where rubric='DD'; count ------- 6753 (1 row)
dba400=# SELECT count(*) from annuncio400 where rubric='MA'; count ------- 2165 (1 row)
so it's using the index on 2000 rows and not for 6000? it's not that
big difference, isn't it?
It's a question of how many pages it thinks it's going to have to retrieve in order to handle the request. If it say needs (or think it needs) to retrieve 50% of the pages, then given a random_page_cost of 4, it's going to expect the index scan to be about twice the cost.
Generally speaking one good way to compare is to try the query with explain analyze and then change parameters like enable_seqscan and try the query with explain analyze again and compare the estimated rows and costs. That'll give an idea of how it expects the two versions of the query to compare speed wise.
Ok then how do you explain this? just created a copy of the same database
Slow seqscan query executed on dba400
dba400=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3116.00 rows=11 width=546) (actual time=46.66..51.40 rows=11 loops=1)
-> Seq Scan on annuncio400 (cost=0.00..35490.60 rows=125 width=546) (actual time=46.66..51.38 rows=12 loops=1)
Filter: ((rubric = 'DD'::bpchar) AND (lower((testo)::text) ~~ 'cbr%'::text))
Total runtime: 51.46 msec
(4 rows)
fastest index scan query on dba400b (exact copy of dba400)
dba400b=# explain analyze SELECT *, oid FROM annuncio400 WHERE rubric = 'DD' AND LOWER(testo) Like LOWER('cbr%') OFFSET 0 LIMIT 11;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..7058.40 rows=9 width=546) (actual time=1.36..8.18 rows=11 loops=1)
-> Index Scan using rubric on annuncio400 (cost=0.00..7369.42 rows=9 width=546) (actual time=1.35..8.15 rows=12 loops=1)
Index Cond: (rubric = 'DD'::bpchar)
Filter: (lower((testo)::text) ~~ 'cbr%'::text)
Total runtime: 8.28 msec
(5 rows)
anyway, shall I try to lower the random_page value since I get an index scan? I mean that in my case I've already noted that with index scan that query get executed in 1/10 of the seqscan speed.
Thank you Edoardo
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
