Re: query performance

["Alex Turner" <armtuk@xxxxxxxxx>]

If you haven't already, make sure you've done a vacuum full recently.  When in doubt, pg_dump the db, and reload it, and see if that helps, but this works for me:

create table overview as select _id from  t_documentcontent;
alter table overview add constraint overview_pkey primary key (_id);

select * from t_documentcontent where _id in (select _id
    FROM overview LIMIT 50 OFFSET 80000);

create a trigger on insert/delete/update to keep this table in sync, or if you don't need to then just re-run the create every so often depending on your needs (I'll be happy to demonstrate the required triggers if you need it).

make sure that you have adequate RAM available for file cache, hitting the disk everytime you query will suck no matter what you do.

Alex

On Jan 13, 2008 9:58 PM, pepone. onrez < pepone.onrez@xxxxxxxxx> wrote:

I have this query in a table with 150 thowsand tuples and it takes to long

    t_documentcontent._id AS _id
    FROM t_documentcontent LIMIT 50 OFFSET 80000

here is the explain output

"Limit  (cost= 100058762.30..100058799.02 rows=50 width=58) (actual time=19433.474..19433.680 rows=50 loops=1)"
"  ->  Seq Scan on t_documentcontent  (cost=100000000.00..100110772.07 rows=150807 width=58) (actual time=53.934..19402.030 rows=80050 loops=1)"
"Total runtime: 19433.748 ms"

here the table structure

CREATE TABLE t_documentcontent(
    _id varchar(60) NOT NULL,
    _filesystem varchar(60) NOT NULL,
    _parent varchar(60) NOT NULL,
    _document varchar(60) NOT NULL,
    _title varchar NOT NULL,
    _resume varchar,
    _content varchar,
    _lang integer NOT NULL,
    _creationdate timestamp NOT NULL DEFAULT now(),
    _updatedate timestamp NOT NULL DEFAULT now(),
    _indexeddate timestamp NOT NULL DEFAULT now(),
    CONSTRAINT documentcontent_pkey PRIMARY KEY (_id),
    CONSTRAINT documentcontent_filesystem_fkey
        FOREIGN KEY (_filesystem) REFERENCES t_filesystem(_id) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT documentcontent_parent_fkey FOREIGN KEY (_parent) REFERENCES t_node(_id) ON DELETE NO ACTION,
    CONSTRAINT documentcontent_document_fkey
        FOREIGN KEY (_document) REFERENCES t_node(_id) ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT documentcontent_lang_lang_fkey FOREIGN KEY (_lang) REFERENCES t_lang(_id)) WITHOUT OIDS;

Any ideas for improve this query performance.