From: Luigi N. Puleio <npuleio@rocketmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: EXPLAIN detail
Date: Wed, 9 Apr 2008 04:05:54 -0700 (PDT)
Message-ID: <931174.41521.qm@web33505.mail.mud.yahoo.com> (raw)
In-Reply-To: <2e78013d0804090304l611044d6q4a71a927ec199b65@mail.gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: npuleio@rocketmail.com
Subject: Re: EXPLAIN detail
In-Reply-To: <931174.41521.qm@web33505.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox