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
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
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 =
napisana przez Gregg Jaskiewicz w dniu 3 paź 2011, o godz. 13:20:
2011/10/3 Nowak Michał michal.no...@me.com:
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
between pages)
- then increasing stats won't help you.
As a test, try clustering the table by the source_id column. Vacuum it
again, and retry. Unfortunately even if that helps, it won't actually
fix it permanently.
you probably need to normalize the table.
2011/10/3 Nowak Michał michal.no