Re: Efficiency of timestamps

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
>