Re: Resources

I don't beleive indexes will improve SELECT using LIKE.

In second query, don't index on tokenid, index on the expression:
(substring(tokenid FROM 0 FOR strpos(tokenid,'A')))

Frank

At 08:45 AM 1/11/02 -0500, Nick Fankhauser wrote:
>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(at)indvalley(dot)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(at)indvalley(dot)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(at)indvalley(dot)com
>> >>
>> >> Life is not fair - get used to it.
>> >> Bill Gates
>> >>
>> >>
>> >> ---------------------------(end of
>> broadcast)---------------------------
>> >> TIP 1: subscribe and unsubscribe commands go to
>> majordomo(at)postgresql(dot)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(at)postgresql(dot)org)
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>