Re: EXPLAIN detail - Mailing list pgsql-performance

--- 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