Re: query performance

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(dot)onrez(at)gmail(dot)com> 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.07rows=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.
>