Since it's my first on this list, I'd like to say "Hi guys" :)
Here is definition of my table:
a9-dev=> \d records;
Table "public.records"
Column|Type | Modifiers
--+--
> How many rows do you have in that table?
a9-dev=> select count(*) from records;
count
-
3620311
(1 row)
a9-dev=> select source_id, count(*) from records where source_id =
'http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml' or source_id =
'http://www.wbc.poznan.pl/dlibra/oai-pmh-r
es.
So, the question is: why planner chooses records_pkey over source_id_id_idx for
the most common value of source_id?
Wiadomość napisana przez Gregg Jaskiewicz w dniu 3 paź 2011, o godz. 13:20:
> 2011/10/3 Nowak Michał :
>>> How many rows do you have in that table?
>>
&
ds2
>> (cost=0.00..3735751.15 rows=970308 width=1124) (actual time=0.074..0.180
>> rows=200 loops=1)
>> Index Cond: ((source_id)::text =
>> 'http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml'::text)
>> Total runtime: 0.235 ms
>> (4 rows)
&
Wiadomość napisana przez Tom Lane w dniu 3 paź 2011, o godz. 17:12:
> I'm thinking it probably sees the pkey index as cheaper because that's
> highly correlated with the physical order of the table. (It would be
> useful to see pg_stats.correlation for these columns.) With a
> sufficiently unsel
Lowering random_page_cost didn't help -- I've tried values 2.0 and 1.5.
Then I tried "order by id -1" hack Marcin Mańk proposed...
a9-dev=> create index foo on records(source_id, (id - 1));
CREATE INDEX
a9-dev=> explain analyze select * from records where source_id
='http://www.wbc.poznan.pl/dl
