Nowak Micha* wrote:
> Lowering random_page_cost didn't help -- I've tried values 2.0 and
> 1.5.
First off, I don't remember you saying how much RAM is on the
system, but be sure to set effective_cache_size to the sum of your
shared_buffers and OS cache. I've often found that the optimizer
unde
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
2011/10/4 Nowak Michał :
>
> a9-dev=> select attname, null_frac, avg_width, n_distinct, correlation from
> pg_stats where tablename = 'records';
> attname | null_frac | avg_width | n_distinct |
> correlation
> --+---+--
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
2011/10/3 Nowak Michał :
> Some info about data distrubution:
>
> a9-dev=> select min(id) from records;
> min
>
> 190830
> (1 row)
>
> a9-dev=> select min(id), max(id) from records where
> source_id='http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml';
> min | max
> -+--
=?iso-8859-2?Q?Nowak_Micha=B3?= writes:
> When I perform query such as this: "select * from records where source_id =
> 'XXX' order by id limit 200;" I expect DB to use index source_id_id_idx with
> XXX as filter. It is true for all but one values of XXX - when I ask for
> records with most c
> a9-dev=> explain analyze select * from records where source_id
> ='http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml' order by id limit
> 200;
> QUERY PLAN
> -
Please compare costs and actual times in those queries:
a9-dev=> explain analyze select * from records where source_id
='http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml' order by id limit 200;
QUERY PLAN
--
2011/10/3 Nowak Michał :
>> 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 =
> 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
How many rows do you have in that table?
I think , that planner thinks that the element you are looking for is
so common - that it will be to expensive to use index to fetch it.
Perhaps try increasing default_statistics_target , and revacuuming the table.
You could also try changing it just for t
11 matches
Mail list logo
