Re: Resources - Mailing list pgsql-sql

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@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
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>