Skip site navigation (1) Skip section navigation (2)

Re: Query performance on session table

From: "Burak Seydioglu" <buraks78(at)gmail(dot)com>
To: "Michael Glaesemann" <grzm(at)seespotcode(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query performance on session table
Date: 2007-06-29 03:28:05
Message-ID: 1b8a973c0706282028p589c7177mf5e75478bbb825b5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
> [Please don't top post as it makes the discussion more difficult to
> follow.]

.... and I thought I was an experienced web user... My apologies.

>  The times you're probably
> interested in are of the sort
>
> SELECT *
> FROM session
> WHERE session_id = ?;
>

This query is really fast as you can see in my original post.

> Also, you should be looking at your app to see what
> queries are actually being run against session and how they perform
> rather than using SELECT * FROM session as a benchmark (unless SELECT
> * FROM session *is* a critical query for your app)


The garbage collector (DELETE FROM session WHERE session_expires <
timestamp) has to do the same sequential scan.

# EXPLAIN ANALYZE DELETE FROM session WHERE session_expires<0;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on session  (cost=0.00..147919.91 rows=1 width=6) (actual
time=1473.284..1473.284 rows=0 loops=1)
   Filter: (session_expires < 0)
 Total runtime: 1473.315 ms
(3 rows)

On a side note however, without me doing anything the results has
changed even though pg_stat_activity table shows the same amount of
activity.

# EXPLAIN ANALYZE SELECT * FROM session;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on session  (cost=0.00..147917.93 rows=793 width=282)
(actual time=1519.959..1612.596 rows=672 loops=1)
 Total runtime: 1613.248 ms
(2 rows)


672 rows in 1.6 secs... I guess I am looking at the wrong place.

Thank you for you input.

Burak

In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-06-29 03:49:08
Subject: Re: Query performance on session table
Previous:From: Michael GlaesemannDate: 2007-06-29 00:56:19
Subject: Re: Query performance on session table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group