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

2011-10-03 Thread Marcin Mańk
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 > -+--

Re: [PERFORM] : Performance Improvement Strategy

2011-10-03 Thread Venkat Balaji
Thanks a lot Kevin ! This email has deepened my understanding on the clustering concept. Keeping this in mind, I have recommended a new disk layout at the OS level for our production servers so that IOs will be balanced on the disks as well. Currently, we do not have mount points divided accordi

Re: [PERFORM] : Performance Improvement Strategy

2011-10-03 Thread Kevin Grittner
Venkat Balaji wrote: > We CLUSTERED a table using mostly used Index. Application is > performing better now. CLUSTER can help in at least four ways: (1) It eliminates bloat in the table heap. (2) It eliminates bloat in the indexes. (3) It can correct fragmentation in the underlying di

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

2011-10-03 Thread Tom Lane
=?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

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

2011-10-03 Thread Cédric Villemain
> 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 > -

Re: [PERFORM] How can i get record by data block not by sql?

2011-10-03 Thread Ben Chobot
On Oct 3, 2011, at 6:52 AM, 姜头 wrote: > How can i get record by data block not by sql? > > I want to read and write lots of data by data blocks and write record to a > appointed data block and read it. > so i can form a disk-resident tree by recording the block address. But i > don't know how

[PERFORM] How can i get record by data block not by sql?

2011-10-03 Thread 姜头
How can i get record by data block not by sql? I want to read and write lots of data by data blocks and write record to a appointed data block and read it. so i can form a disk-resident tree by recording the block address. But i don't know how to implement in postgresql. Is there system f

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

2011-10-03 Thread Nowak Michał
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 --

Fwd: [PERFORM] Query with order by and limit is very slow - wrong index used

2011-10-03 Thread Nowak Michał
Setting statistics to 1000 on id and source_id didn't solve my problem: a9-dev=> explain analyze select * from records where source_id ='http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml' order by id limit 200; QUERY PLAN

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

2011-10-03 Thread Gregg Jaskiewicz
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 =

Re: [PERFORM] : Performance Improvement Strategy

2011-10-03 Thread Venkat Balaji
Hello, Thanks for your suggestions ! We CLUSTERED a table using mostly used Index. Application is performing better now. Thanks VB On Tue, Sep 27, 2011 at 6:01 PM, Venkat Balaji wrote: > Forgot to mention - > > Kevin, > > CLUSTER seems to be an very interesting concept to me. > > I am thinking

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

2011-10-03 Thread 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 = 'http://www.wbc.poznan.pl/dlibra/oai-pmh-r

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

2011-10-03 Thread Gregg Jaskiewicz
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

[PERFORM] Query with order by and limit is very slow - wrong index used

2011-10-03 Thread Nowak Michał
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 --+--