pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nick Fankhauser <nickf@ontko.com>
To: Guru\(External\) <guru@indvalley.com>
To: pgsql-sql@postgresql.org
Subject: Re: Resources
Date: Fri, 11 Jan 2002 08:45:18 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGEEOHECAA.nickf@ontko.com> (raw)
In-Reply-To: <4333291666.20020111183320@indvalley.com>

The explains indicate that a sequential scan is happening, so you want to
index the columns involved.

For instance for query No. 1, create an index on tickettab.arrivaldate,
tickettab.subcode, subscribertab.custcode, subscribertab.address1 &
subscribertab.address2.

This will hurt your performance on inserts, but should really help the
query. Try doing an explain again with these indexes on to see how it
changes the situation. If inserts are an issue, you may need to play with it
a bit to find the indexes that give you the best gain.

-Nick



> /* QUERY No 1
>    ---------- */
>
> select count(tickettab.tokenid) as ticketcount
> from tickettab,subscribertab
> where (tickettab.arrivaldate >='2001-12-12'
> and tickettab.arrivaldate <='2002-01-12') and
> tickettab.subcode=subscribertab.custcode and
> ((subscribertab.address1 ILIKE '%Cunningham%') OR
> (subscribertab.address2 ILIKE '%Cunningham%'))
>
> /* QUERY No 2
>    ---------- */
>
> select count(ticketmultab.tokenid) as ticketmulcount
> from ticketmultab,subscribertab
> where (ticketmultab.arrivaldate >='2001-12-12'
> and ticketmultab.arrivaldate <='2002-01-12') and
> (substring(tokenid FROM 0 FOR
> strpos(tokenid,'A')))=subscribertab.custcode and
> ((subscribertab.address1 ILIKE '%Cunningham%') OR
> (subscribertab.address2 ILIKE '%Cunningham%'))
>
>
> ######################EXPLAINS ON QUERIES ########################
>
> /* EXPLAIN ON QUERY No 1
>    --------------------- */
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=276.27..276.27 rows=1 width=28)
>   ->  Nested Loop  (cost=0.00..276.26 rows=1 width=28)
>         ->  Seq Scan on subscribertab  (cost=0.00..265.87 rows=1 width=8)
>         ->  Seq Scan on tickettab  (cost=0.00..8.40 rows=160 width=20)
>
> /* EXPLAIN ON QUERY No 1
>    --------------------- */
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=269.02..269.02 rows=1 width=20)
>   ->  Nested Loop  (cost=0.00..269.02 rows=1 width=20)
>         ->  Seq Scan on subscribertab  (cost=0.00..265.87 rows=1 width=8)
>         ->  Seq Scan on ticketmultab  (cost=0.00..2.50 rows=33 width=12)
>
>
> ################ END OF REQUIRED DATA ########################
>
> Thanks in advance I hope I have provided the required information.
> Basically, I need to optimize my operations, but changing the table
> structures may be too late in the day.
>
> --
> Best regards,
>  Gurudutt                            mailto:guru@indvalley.com
>
> Life is not fair - get used to it.
> Bill Gates
>
>
> Thursday, January 10, 2002, 9:20:36 PM, you wrote:
>
>
> JE> You've shown that the query takes up a lot of CPU, and that it is
> JE> slower than you would like.  We have an idea as to how big the table
> JE> is (not very), and we know that you have done your homework and have
> JE> vacuum analyzed.
>
> JE> Next we need the query in question (so we can check for some of the
> JE> more well known performance pitfalls like using IN on large result
> JE> sets), and also the explain output (so we can see what PostgreSQL
> JE> thinks of your query).  You also might want to consider including the
> JE> schema of the tables involved.
>
> JE> Jason
>
> JE> Gurudutt <guru@indvalley.com> writes:
>
> >> Hi,
> >>
> >> I have a pentium III server, running on RHL 7.1 with 256 MB RAM,
> >>
> >> The following is output of the "top" command for query which involves
> >> fetch from a table with about MAX of 10,000 rows.
> >>
> >> -------------------------------------TOP------------------------------
> >>   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
> >>
> >>  3174 postgres  19   0  3328 3328  2672 R    99.0  1.3   0:58
> postmaster
> >>  1199 nobody     9   0  3728 3728  2704 S     0.5  1.4   0:03 httpd
> >>  3035 root      10   0  1048 1048   840 R     0.3  0.4   0:15 top
> >>     1 root       8   0   544  544   472 S     0.0  0.2   0:04 init
> >>     2 root       9   0     0    0     0 SW    0.0  0.0   0:00 keventd
> >>     3 root
> >>
> >>
> >> Now, my question is, it takes ages(2 mints) for the query to
> run (regularly
> >> VACUUM ANALYZED Database) and if you look closely at the resources
> >> consumed by the postgres, it is almost taking away 100% CPU time.
> >>
> >> How can we make it faster and to consume less resources ??
> >>
> >> Can anybody suggest the steps they are taking for time-critical
> >> applications to run efficiently.
> >>
> >> Thanks in advance
> >>
> >> --
> >> Best regards,
> >>  Gurudutt                          mailto:guru@indvalley.com
> >>
> >> Life is not fair - get used to it.
> >> Bill Gates
> >>
> >>
> >> ---------------------------(end of
> broadcast)---------------------------
> >> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> JE> ---------------------------(end of
> broadcast)---------------------------
> JE> TIP 2: you can get off all lists at once with the unregister command
> JE>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>




view thread (18+ messages)  latest in thread

Message-ID: <NEBBLAAHGLEEPCGOBHDGEEOHECAA.nickf@ontko.com>
Permalink:  ../NEBBLAAHGLEEPCGOBHDGEEOHECAA.nickf@ontko.com/
Also on:    postgresql.org/message-id/NEBBLAAHGLEEPCGOBHDGEEOHECAA.nickf@ontko.com

 · 

reply

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-sql@postgresql.org
  Cc: nickf@ontko.com, guru@indvalley.com
  Subject: Re: Resources
  In-Reply-To: <NEBBLAAHGLEEPCGOBHDGEEOHECAA.nickf@ontko.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