Re: Slow queries when ORDER BY ... DESC with table inheritance (no index scan backwards) - Mailing list pgsql-general

Thank you Tomas for the reply.

I'm including the requested information below. To sumarize my problem:
The same query with "order by desc" on 2 tables (concrete and abstract).
A backward PK index scan is used when run on the concrete table, but not
when run on the abstract table.

a) Was the table analyzed recently? Is the table vacuumed regularly?

Yes and yes.

b) How large are the tables? (Number of tuples and pages. SELECT
reltuples, relpages FROM pg_class WHERE relname LIKE 'event%')

select relname, reltuples, relpages FROM pg_class WHERE relname LIKE
'event%';
     relname     | reltuples  | relpages
-----------------+------------+----------
 event           |          0 |        0
 event_1_pkey    | 2.3961e+07 |   160546
 event_1         | 2.3961e+07 |   674973

c) What values are used for the important config options (work_mem is
important here)

Very little. We have few ressources on this server, its main task is not
database server, we just use the database as a log container.

These are the few changes we made to the defaults:
shared_buffers = 1000
max_connections = 300
stats_start_collector = on
stats_row_level = on
autovacuum = on

d) What is the basic hw config (disk drives, etc.)

Small server. Single CPU, 2GB ram (a lot used by Java processes), 60GB
single HD with Linux ext3.

e) Are there any indexes on the tables? Try to create index on columns
used in the "order by" clause.

The primary key is already used by the where clause. If the query is run
on the inherited table directly, it will use the primary key index
backwards.

f) What is the exact query you're optimizing? Run it with EXPLAIN
ANALYZE and post the output here.

On the "concrete" table (event_1):
gsmlog3_30=# explain analyze select * from event_1 where timestamp >
1226952050683 order by timestamp desc limit 10;
                                                                  QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------------
 Limit  (cost=0.00..17.85 rows=10 width=740) (actual time=87.635..87.635
rows=0 loops=1)
   ->  Index Scan Backward using event_1_pkey on event_1
(cost=0.00..4239.99 rows=2376 width=740) (actual time=87.631..87.631
rows=0 loops=1)
         Index Cond: ("timestamp" > 1226952050683::bigint)
 Total runtime: 87.685 ms

On the "abstract" table (event):
gsmlog3_30=# explain analyze select * from event where timestamp >
1226952050683 order by timestamp desc limit 10;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------
 Limit  (cost=2136380.76..2136380.78 rows=10 width=4188) (actual
time=43722.625..43722.638 rows=10 loops=1)
   ->  Sort  (cost=2136380.76..2136892.78 rows=204811 width=4188)
(actual time=43722.623..43722.631 rows=10 loops=1)
         Sort Key: public.event."timestamp"
         ->  Result  (cost=1.01..16192.40 rows=204811 width=4188)
(actual time=142.393..927.236 rows=206379 loops=1)
               ->  Append  (cost=1.01..16192.40 rows=204811 width=4188)
(actual time=142.390..631.351 rows=206379 loops=1)
                     ->  Bitmap Heap Scan on event  (cost=1.01..7.49
rows=3 width=4188) (actual time=29.491..29.491 rows=0 loops=1)
                           Recheck Cond: ("timestamp" >
1226952050683::bigint)
                           ->  Bitmap Index Scan on event_pkey
(cost=0.00..1.01 rows=3 width=0) (actual time=29.488..29.488 rows=0
loops=1)
                                 Index Cond: ("timestamp" >
1226952050683::bigint)
                     ->  Seq Scan on event_a event  (cost=0.00..10.50
rows=40 width=1831) (actual time=0.002..0.002 rows=0 loops=1)
                           Filter: ("timestamp" > 1226952050683::bigint)
                     ->  Bitmap Heap Scan on event_b event
(cost=1.01..7.49 rows=3 width=4188) (actual time=0.018..0.018 rows=0
loops=1)
                           Recheck Cond: ("timestamp" >
1226952050683::bigint)
                           ->  Bitmap Index Scan on event_b_pkey
(cost=0.00..1.01 rows=3 width=0) (actual time=0.016..0.016 rows=0
loops=1)
                                 Index Cond: ("timestamp" >
1226952050683::bigint)
                     ->  Index Scan using event_1_pkey on event_2 event
(cost=0.00..4239.99 rows=2376 width=740) (actual time=112.812..112.812
rows=0 loops=1)
                           Index Cond: ("timestamp" >
1226952050683::bigint)
                     ->  Seq Scan on event_1 event  (cost=0.00..11926.94
rows=202389 width=845) (actual time=0.061..295.034 rows=206379 loops=1)
                           Filter: ("timestamp" > 1226952050683::bigint)
 Total runtime: 43969.473 ms


-----Original Message-----
>
> A lot of important information is missing in your post, for example:
>
> a) Was the table analyzed recently? Is the table vacuumed regularly?
> b) How large are the tables? (Number of tuples and pages. SELECT
> reltuples, relpages FROM pg_class WHERE relname LIKE 'event%')
> c) What values are used for the important config options (work_mem is
> important here)
> d) What is the basic hw config (disk drives, etc.)
> e) Are there any indexes on the tables? Try to create index on columns
> used in the "order by" clause.
> f) What is the exact query you're optimizing? Run it with EXPLAIN
> ANALYZE
> and post the output here.
>
> regards
> Tomas
>
>
> > Hi all,
> >
> > I'm a newbie to Postgres so please bear with me. I have a schema
that
> > uses inherited tables. I need the queries on my 'event' table to
> always
> > be in descending order of the primary key, i.e. scan the index
> backwards
> > (for obvious performance reasons). Somehow the ORDER BY doesn't seem
> to
> > be propagated to the inherited tables (event_a), hence no backward
> index
> > scan.
> >
> > Here's an example query:
> > select * from event where timestamp < 1234567890 order by timestamp
> > desc;
> >
> > I'm using version 8.1.3.
> >
> > I haven't found any relevant information in the docs or the mailing
> > lists. Is this a known bug? Is there a workaround?
> >
> > Thanks in advance.
> > Luke
> >
> > ------------------------------------
> >
> > CREATE TABLE event (
> >   timestamp BIGINT NOT NULL,
> >   gsmTimestamp BIGINT NOT NULL,
> >   alarmURI VARCHAR(255) NOT NULL,
> >   alarmName VARCHAR(255),
> >   deviceURI VARCHAR(255),
> >   deviceClass VARCHAR(255),
> >   typeId INTEGER NOT NULL,
> >   userName VARCHAR(255),
> >   groupPath VARCHAR(255),
> >   oldState INTEGER NOT NULL,
> >   newState INTEGER NOT NULL,
> >   oldLatch INTEGER NOT NULL,
> >   newLatch INTEGER NOT NULL,
> >   oldAck INTEGER NOT NULL,
> >   newAck INTEGER NOT NULL,
> >   oldMode INTEGER NOT NULL,
> >   newMode INTEGER NOT NULL,
> >   timecode bigint NOT NULL,
> >   text VARCHAR(255),
> >   extraInfo VARCHAR(255),
> >   PRIMARY KEY (timestamp, alarmURI)
> > );
> >
> > CREATE TABLE event_a (
> >   PRIMARY KEY (timestamp, alarmURI)
> > ) inherits (event);
> >
> > CREATE TABLE event_b (
> >   PRIMARY KEY (timestamp, alarmURI)
> > ) inherits (event);
> >
> > CREATE TABLE event_1 (
> >   PRIMARY KEY (timestamp, alarmURI)
> > ) inherits (event);
> >
> > CREATE or REPLACE RULE insert_to_event AS
> > ON INSERT TO event DO INSTEAD
> > INSERT INTO event_a ("timestamp", gsmtimestamp, alarmuri, alarmname,
> > deviceuri, deviceclass, typeid, username, grouppath, oldstate,
> newstate,
> > oldlatch, newlatch, oldack, newack, oldmode, newmode, timecode,
text,
> > extrainfo)
> > VALUES (new."timestamp", new.gsmtimestamp, new.alarmuri,
> new.alarmname,
> > new.deviceuri, new.deviceclass, new.typeid, new.username,
> new.grouppath,
> > new.oldstate, new.newstate, new.oldlatch, new.newlatch, new.oldack,
> > new.newack, new.oldmode, new.newmode, new.timecode, new.text,
> > new.extrainfo);
> >
> >
> > CREATE TABLE eventCause (
> >   eventTimestamp BIGINT NOT NULL,
> >   eventURI VARCHAR(255) NOT NULL,
> >   causeTimestamp BIGINT NOT NULL,
> >   causeURI VARCHAR(255) NOT NULL,
> >   PRIMARY KEY (eventTimestamp, eventURI, causeURI, causeTimestamp)
> > );
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>