Hi,

I am running on postgres 7.4.6.
I did a vacuum analyze on the database but there was no change.
I Attached here a file with details about the tables, the queries and
the Explain analyze plans.
Hope this can be helpful to analyze my problem

10x
Doron

Search Discussions

  • Ruben Rubio Rey at Apr 20, 2006 at 10:48 am
    I think that the problem is the GROUP BY (datetime) that is
    date_trunc('hour'::text, i.entry_time)
    You should create an indexe with this expression (if its possible).

    http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html

    If is not possible, I would create a column with value
    date_trunc('hour'::text, i.entry_time) of each row and then index it.

    Hope this helps :)

    Doron Baranes wrote:
    Hi,

    I am running on postgres 7.4.6.
    I did a vacuum analyze on the database but there was no change.
    I Attached here a file with details about the tables, the queries and
    the Explain analyze plans.
    Hope this can be helpful to analyze my problem

    10x
    Doron


    ------------------------------------------------------------------------


    ---------------------------(end of broadcast)---------------------------
    TIP 9: In versions below 8.0, the planner will ignore your desire to
    choose an index scan if your joining column's datatypes do not
    match
  • Doron Baranes at Apr 20, 2006 at 11:57 am
    Ok. But that means I need a trigger on the original column to update the
    new column on each insert/update and that overhead.

    -----Original Message-----
    From: Ruben Rubio Rey
    Sent: Thursday, April 20, 2006 12:49 PM
    To: Doron Baranes; pgsql-performance@postgresql.org
    Subject: Re: [PERFORM] Perfrmance Problems (7.4.6)

    I think that the problem is the GROUP BY (datetime) that is
    date_trunc('hour'::text, i.entry_time)
    You should create an indexe with this expression (if its possible).

    http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html

    If is not possible, I would create a column with value
    date_trunc('hour'::text, i.entry_time) of each row and then index it.

    Hope this helps :)

    Doron Baranes wrote:
    Hi,

    I am running on postgres 7.4.6.
    I did a vacuum analyze on the database but there was no change.
    I Attached here a file with details about the tables, the queries and
    the Explain analyze plans.
    Hope this can be helpful to analyze my problem

    10x
    Doron


    ----------------------------------------------------------------------- -

    ---------------------------(end of
    broadcast)---------------------------
    TIP 9: In versions below 8.0, the planner will ignore your desire to
    choose an index scan if your joining column's datatypes do not
    match
  • Ruben Rubio Rey at Apr 20, 2006 at 2:54 pm
    Did you tried to index the expression?
    Did it work?

    Doron Baranes wrote:
    Ok. But that means I need a trigger on the original column to update the
    new column on each insert/update and that overhead.

    -----Original Message-----
    From: Ruben Rubio Rey
    Sent: Thursday, April 20, 2006 12:49 PM
    To: Doron Baranes; pgsql-performance@postgresql.org
    Subject: Re: [PERFORM] Perfrmance Problems (7.4.6)

    I think that the problem is the GROUP BY (datetime) that is
    date_trunc('hour'::text, i.entry_time)
    You should create an indexe with this expression (if its possible).

    http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html

    If is not possible, I would create a column with value
    date_trunc('hour'::text, i.entry_time) of each row and then index it.

    Hope this helps :)

    Doron Baranes wrote:


    Hi,

    I am running on postgres 7.4.6.
    I did a vacuum analyze on the database but there was no change.
    I Attached here a file with details about the tables, the queries and
    the Explain analyze plans.
    Hope this can be helpful to analyze my problem

    10x
    Doron


    -----------------------------------------------------------------------
    -

    ---------------------------(end of
    broadcast)---------------------------

    TIP 9: In versions below 8.0, the planner will ignore your desire to
    choose an index scan if your joining column's datatypes do not
    match



    ---------------------------(end of broadcast)---------------------------
    TIP 2: Don't 'kill -9' the postmaster


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedApr 20, '06 at 7:24a
activeApr 20, '06 at 2:54p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Doron Baranes: 2 posts Ruben Rubio Rey: 2 posts

People

Translate

site design / logo © 2022 Grokbase