From: idc danny <idcdanny@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Problem with 11 M records table
Date: Tue, 13 May 2008 09:57:03 -0700 (PDT)
Message-ID: <684043.20585.qm@web35307.mail.mud.yahoo.com> (raw)
Hi everybody,
I'm fairly new to PostgreSQL and I have a problem with
a query:
SELECT * FROM "LockerEvents" LIMIT 10000 OFFSET
10990000
The table LockerEvents has 11 Mlillions records on it
and this query takes about 60 seconds to complete.
Moreover, even after making for each column in the
table a index the EXPLAIN still uses sequential scan
instead of indexes.
The EXPLAIN is:
"Limit (cost=100245579.54..100245803.00 rows=10000
width=60) (actual time=58414.753..58482.661 rows=10000
loops=1)"
" -> Seq Scan on "LockerEvents"
(cost=100000000.00..100245803.00 rows=11000000
width=60) (actual time=12.620..45463.222 rows=11000000
loops=1)"
"Total runtime: 58493.648 ms"
The table is:
CREATE TABLE "LockerEvents"
(
"ID" serial NOT NULL,
"IDMoneySymbol" integer NOT NULL,
"IDLocker" integer NOT NULL,
"IDUser" integer NOT NULL,
"IDEventType" integer NOT NULL,
"TimeBegin" timestamp(0) without time zone NOT NULL,
"Notes" character varying(200),
"Income" double precision NOT NULL DEFAULT 0,
"IncomeWithRate" double precision NOT NULL DEFAULT
0,
CONSTRAINT pk_lockerevents_id PRIMARY KEY ("ID"),
CONSTRAINT fk_lockerevents_ideventtype_eventtypes_id
FOREIGN KEY ("IDEventType")
REFERENCES "EventTypes" ("ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_lockerevents_idlocker_lockers_id
FOREIGN KEY ("IDLocker")
REFERENCES "Lockers" ("ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT
fk_lockerevents_idmoneysymbol_moneysymbols_id FOREIGN
KEY ("IDMoneySymbol")
REFERENCES "MoneySymbols" ("ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_lockerevents_iduser_users_id FOREIGN
KEY ("IDUser")
REFERENCES "Users" ("ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
CREATE INDEX idx_col_lockerevents_income
ON "LockerEvents"
USING btree
("Income");
CREATE INDEX idx_col_lockerevents_incomewithrate
ON "LockerEvents"
USING btree
("IncomeWithRate");
CREATE INDEX idx_col_lockerevents_notes
ON "LockerEvents"
USING btree
("Notes");
CREATE INDEX idx_col_lockerevents_timebegin
ON "LockerEvents"
USING btree
("TimeBegin");
CREATE INDEX
idx_fk_lockerevents_ideventtype_eventtypes_id
ON "LockerEvents"
USING btree
("IDEventType");
CREATE INDEX idx_fk_lockerevents_idlocker_lockers_id
ON "LockerEvents"
USING btree
("IDLocker");
CREATE INDEX
idx_fk_lockerevents_idmoneysymbol_moneysymbols_id
ON "LockerEvents"
USING btree
("IDMoneySymbol");
CREATE INDEX idx_fk_lockerevents_iduser_users_id
ON "LockerEvents"
USING btree
("IDUser");
CREATE UNIQUE INDEX idx_pk_lockerevents_id
ON "LockerEvents"
USING btree
("ID");
If I do the query :
SELECT * FROM "LockerEvents" LIMIT 10000 OFFSET 0
then this query takes under a second to complete - I
believe this is because the sequential scan starts
from beginning.
I need the query to complete under 10 seconds and I do
not know how to do it.
Please help me!
Thank you,
Danny
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: idcdanny@yahoo.com
Subject: Re: Problem with 11 M records table
In-Reply-To: <684043.20585.qm@web35307.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox