Re: Query with order by and limit is very slow - wrong index used

> 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-repository.xml' group by source_id;
                      source_id                        | count  
--------------------------------------------------------+--------
http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml    | 427254
http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml | 989184
(2 rows)


> ALTER TABLE records ALTER id SET source_id 1000; vacuum analyze verbose records;
Did you mean ALTER TABLE records ALTER id SET STATISTICS 1000;?




Wiadomość napisana przez Gregg Jaskiewicz w dniu 3 paź 2011, o godz. 12:02:

> 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 the column:
> 
> ALTER TABLE records ALTER id SET source_id 1000; vacuum analyze verbose records;
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance