Re: Problem with 11 M records table - Mailing list pgsql-performance

In response to idc danny <idcdanny@yahoo.com>:

> Hi everybody,
>
> I'm fairly new to PostgreSQL and I have a problem with
> a query:
>
> SELECT * FROM "LockerEvents" LIMIT 10000 OFFSET
> 10990000

This query makes no sense, and I can't blame PostgreSQL for using a
seq scan, since you've given it no reason to do otherwise.  If you
want a random sampling of rows, you should construct your query more
to that effect, as this query is going to give you a random sampling
of rows, and the LIMIT/OFFSET are simply junk that confuses the
query planner.

I suspect that you don't really want a random sampling of rows, although
I can't imagine what you think you're going to get from that query.
Have you tried putting an ORDER BY clause in?

>
> 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
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************