DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=rocketmail.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=kU8QJSJ7nuwU0Ve/ZgLV6fuenZvfmn2mzldDzXgi3NJGnrbuZ06ZV9h7dgY2ZRl/vJsJ6ssbTJrmsuX/OjCHxyTRnJMGE5HgiYTo4XA5z/PZStyiF6D3CJh5dD45nIJO8mh5ked2CNjCUV/9Wx6NukRFlOku0QzE38OOerwjYG4=;
Date: Wed, 9 Apr 2008 04:05:54 -0700 (PDT)
From: "Luigi N. Puleio" <npuleio@rocketmail.com>
Subject: Re: EXPLAIN detail
To: pgsql-performance@postgresql.org
In-Reply-To: <2e78013d0804090304l611044d6q4a71a927ec199b65@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <931174.41521.qm@web33505.mail.mud.yahoo.com>

--- Pavan Deolasee <pavan.deolasee@gmail.com> wrote:

> On Wed, Apr 9, 2008 at 3:21 PM, Luigi N. Puleio
> <npuleio@rocketmail.com> wrote:
> > Hello everyone!!
> >
> >  I have a table with 17 columns and it has almost
> >  530000 records and doing just a
> >
> >  SELECT * FROM table
> >
> >  with the EXPLAIN ANALYZE I get:
> >
> >  Seq Scan on table (cost=0.00...19452.95
> rows=529395
> >  width=170) (actual time=0.155...2194.294
> rows=529395
> >  loops=1)
> >  total runtime=3679.039 ms
> >
> >  and this table has a PK...
> >  Do you think is too much time for a simple
> select?...
> >
> 
> Well, PK won't help you here because you are
> selecting all rows
> from the table and that seq scan is the right thing
> for that.
> Without knowing your hardware its difficult to judge
> if
> the time taken is more or not. Anyways, I don't
> think there is much
> tweaking you can do for such a query except making
> sure that
> your table is not bloated with dead tuples.
> 

In effect, this simple query is a start of examination
to check about speed for another nested query; more
precisely I'm tring to obtain the difference of the
time querying the same table with a different
condition, like:

SELECT 
     (a.column1)::date, MIN(b.column2) - a.column2
FROM 
     table a
     inner join table b 
     on ((a.column1)::date = (b.column1)::date amd
b.column3 = 'b' and (b.column1)::time without time
zone >= (a.column1)::time without time zone)
WHERE 
    (a.column1)::date = '2008-04-09'
    a.column3 = 'a'
GROUP BY a.column1

and with this I have to obtain like 3-4 records from
all those whole 500000 records and with the explain
analyze I get almost 6 seconds:

Nested Loop (cost=0.00...52140.83 rows=1 width=34)
(actual time=4311.756...5951.271 rows=1 loops=1)

So its been a lot of time because I could wonder how
long it would take for example if I do a filter not
for a single day but for a month which should return
much more than 1 row...

Actually I emailed to the responsible of the server
where PostgreSQL is installed to see if he done a
vacuum manually lately since querying the pg_settings
or the pg_stat_all_tables I have no response about
autovacuum...

But maybe there's a better way to query this nested
loop for more efficience....

Thanks to all!
Ciao,
Luigi

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com