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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance