Re: Efficiency of timestamps - Mailing list pgsql-performance

On Tue, 8 Jul 2003, Martin Foster wrote:

> As I keep looking through code to see where I can make things more
> efficient, I noticed that in some cases timestamps seem horribly
> inefficient.   This leads to very long run times for certain queries.
>
> Here is an example:
>
> -- USING TIMESTAMPS TO NARROW DOWN --
>
> SELECT
>   Post.PostIDNumber,
>   Post.PuppeteerLogin,
>   Post.PuppetName,
>   Post.PostCmd,
>   Post.PostClass
>   FROM ethereal.Post
>   WHERE Post.PostTimeStamp > (LOCALTIMESTAMP - INTERVAL '10 Minutes')
>   AND Post.RealmName='Amalgam'
>   AND (Post.PostTo='all' OR Post.PostTo='root')
>   AND (NOT EXISTS (SELECT PuppetIgnore.PuppetLogin
>   FROM ethereal.PuppetIgnore
>   WHERE PuppetIgnore.PuppetIgnore='global'
>   AND PuppetIgnore.PuppeteerLogin='root'
>   AND PuppetIgnore.PuppetLogin=Post.PuppeteerLogin)
>   OR Post.PuppeteerLogin IS NULL)
>   AND (NOT EXISTS (SELECT PuppetIgnore.PuppetName
>   FROM ethereal.PuppetIgnore
>   WHERE PuppetIgnore='single'
>   AND PuppetIgnore.PuppeteerLogin='root'
>   AND PuppetIgnore.PuppetName=Post.PuppetName)
>   OR Post.PuppetName IS NULL)
>   ORDER BY Post.PostIDNumber LIMIT 100
>
> -- Explain of Above--
> Limit  (cost=0.00..260237.32 rows=100 width=48)
>    ->  Index Scan using pkpost on post  (cost=0.00..3020594.00 rows=1161
> width=48)
>          Filter: ((posttimestamp > (('now'::text)::timestamp(6) without
> time zone - '00:10'::interval)) AND (realmname = 'Amalgam'::character
> varying) AND ((postto = 'all'::character varying) OR (postto =
> 'root'::character varying)) AND ((NOT (subplan)) OR (puppeteerlogin IS
> NULL)) AND ((NOT (subplan)) OR (puppetname IS NULL)))

I think you might get better results with some kind of multi-column index.
It's using the index to avoid a sort it looks like, but it's not helping
to find the conditions. I can't remember the correct ordering, but maybe
(posttimestamp, realmname, postidnumber).  Having separate indexes on the
fields won't help currently since only one index will get chosen for the
scan.  Also, what does explain analyze show?


> -- NAME        : Post
> -- REFERENCES  : Realm*
> --               Puppet*
> --               PuppeteerLogin*
> --
> -- DESCRIPTION : Post is the hive of activity for all realms.
> Associated with all three
> --               major tables, it is not actually linked because of the
> nature of storing
> --               posts for statistics and auditing.
>
> CREATE TABLE ethereal.Post (
>    PostIDNumber         INT           NOT NULL DEFAULT
> NEXTVAL('ethereal.seqPost'),
>    RealmName            VARCHAR(30)   NOT NULL,
>    PuppetName           VARCHAR(30),
>    PuppeteerLogin       VARCHAR(10),
>    PostTo               VARCHAR(30),
>    PostTimestamp        TIMESTAMP     NOT NULL DEFAULT LOCALTIMESTAMP,
>    PostClass            VARCHAR(10)   NOT NULL DEFAULT 'general',
>    PostCmd              VARCHAR(10)   NOT NULL DEFAULT 'none',
>    PostFullFormat       TEXT,
>    PostImagelessFormat  TEXT,
>    PostPartialFormat    TEXT,
>    CONSTRAINT pkPost                  PRIMARY KEY (PostIDNumber),
>    CONSTRAINT enumPostClass           CHECK (PostCLass IN
> ('banner','dice','duplicate','general','play','private','special','system')),
>    CONSTRAINT enumPostCmd             CHECK (PostCmd IN
> ('general','none','play','stream'))
> ) WITHOUT OIDS;
>
> -- STANDARD INDEX
> CREATE INDEX idxPost_Class ON ethereal.Post
> (
>    PostClass
> );
>
> CREATE INDEX idxPost_Login ON ethereal.Post
> (
>    PuppeteerLogin
> );
>
> CREATE INDEX idxPost_Puppet ON ethereal.Post
> (
>    PuppetName
> );
>
> CREATE INDEX idxPost_Realm ON ethereal.Post
> (
>    RealmName
> );
>
> CREATE INDEX idxPost_Timestamp ON ethereal.Post
> (
>    PostTimestamp
> );
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>