Re: slow queries on large syslog table

ok antonio,

heres what i did :

    create index syslog_table_sh_sc_st_index on syslog_table (shostid,
sciscomsgid, stimestamp);

    vacuum analyze;

    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (23,3))
AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000;
    matching messages - 49
    query time(s) - 114

    EXPLAIN SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN
(23,3)) AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000;
    Limit  (cost=77745.03..77745.03 rows=1000 width=24)
  ->  Sort  (cost=77745.03..77745.03 rows=17715 width=24)
        ->  Seq Scan on syslog_table  (cost=0.00..76285.88 rows=17715
width=24)

    EXPLAIN SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN
(23,3)) AND (sciscomsgid IN (41,32,70));
    Seq Scan on syslog_table  (cost=0.00..76285.88 rows=17715 width=24)

so maybe the index is ignored because the timestamp isnt in the query:

    EXPLAIN SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (stimestamp
>= '2001-11-14 00:00'::timestamp) AND (shostid IN (30,22)) AND (sciscomsgid
IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000;
    Limit  (cost=82190.08..82190.08 rows=1000 width=24)
  ->  Sort  (cost=82190.08..82190.08 rows=17702 width=24)
        ->  Seq Scan on syslog_table  (cost=0.00..80731.90 rows=17702
width=24)

so how fast is it without the index?
    drop index syslog_table_sh_sc_st_index;

    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (30,22))
AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
    matching messages - 49
    query time(s) - 77

faster!!! sorry my results are so confusing,

colm

-----Original Message-----
From: Antonio Fiol Bonnin [mailto:fiol@w3ping.com]
Sent: 14 December 2001 08:14
To: colm ennis
Cc: PostgreSQL General Mailing list
Subject: Re: [GENERAL] slow queries on large syslog table


Hi again!

For a table that contains an URL id (~2000 different ones), a zone id
(max. 30 different ones), a timestamp (one every 3 hours for every pair
(url, zone) and 5 results columns for the combination (url, zone, time).

So, I get about 2000*(30/3)*1 INSERTs on that table every 3 hours. That
makes a 5 Million tuples table of it by now. 1/3 is an estimative
factor, as not every couple (url, zone) is valid.

For queries, I always ask the same type of information, more or less:

SELECT * FROM T WHERE stamp BETWEEN xxxxx AND yyyyy AND (zone=zz OR
zone=ww [...]) AND url=uuuu;
where xxxxx, yyyyy, zz, ww and uuuu are arbitrary constants of the
needed types.

Requests from that table take about 1 second. An index using all three
columns (url, zone, stamp), in that order is created.

If that index is not present, or is the wrong order, nothing works
(requests take for ages).

WHICH LEADS ME TO THINK THAT I GAVE YOU THE WRONG ANSWER!!!!!

Put the timestamp as the last column at the index.

It is MUCH better if the first columns match as EXACTLY as possible. If
an index matches a range, results may be good, BUT if it matches a
CONSTANT, results are wonderful!!

Well, maybe I am going a bit too far with my last sentence, but you get
it, don't you?

With the idea about the phone book again. You were true that you'd
better go directly to the page containing the exact data you need than
searching for any data range at all.

BUT FORGET about the index size, unless space is a major limitation for
you. These days, disk space is cheap ;-)

Sincerely, It is really worth trying.

Antonio


colm ennis wrote:

>hi all,
>
>thanks for your help, its comforting but also kinda scary to know
>im not the only one whos having trouble!
>
>in response to questions....
>
>as i mentioned before, the syslog_table is currently holds about
>1.7 million rows and is constantly slowly growing, the hostid_table
>and ciscomdgid_table each hold about 80 rows.
>
>the number of rows returned when i ran the query below :
>    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (23,3))
>AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
>was 19 in all cases, so i guess? the row estimations are woefully
>inaccurate.
>
>i ran a vacuum analyse a few minutes prior to trying these queries.
>
>my postgresql is version 7.1.3, is running on freebsd 4.3 and is the
>standard ports install.
>
>with regard to resources, heres the output of top mid select :
>    last pid: 77402;  load averages:  0.17,  0.08,  0.03
>up 87+05:05:42  23:36:25
>    48 processes:  2 running, 46 sleeping
>    CPU states: 11.6% user,  0.0% nice,  5.4% system,  0.0% interrupt, 82.9%
>idle
>    Mem: 47M Active, 139M Inact, 52M Wired, 8784K Cache, 35M Buf, 656K Free
>    Swap:
>
>      PID USERNAME PRI NICE  SIZE    RES STATE    TIME   WCPU    CPU COMMAND
>    77400 pgsql      2   0  5956K  3568K RUN      0:04 21.08% 12.94% postgres
>    77399 www        2   0  7896K  6776K select   0:01  2.32%  1.46% perl
>    77257 pgsql      2   0  7292K  4832K sbwait   0:04  0.05%  0.05% postgres
>    65374 root      10   0  3440K  2696K nanslp  41:00  0.00%  0.00% perl
>    74942 pgsql      2   0  8048K  5876K sbwait   8:46  0.00%  0.00% postgres
>    75116 root       2   0  2148K  1124K poll     1:30  0.00%  0.00% syslogd
>(hmm ...seems like no swap device is configured, but theres loads of
>inactive pages anyway i guess)
>
>memory :
>    hw.physmem: 264351744
>cpu :
>    CPU: Pentium III/Pentium III Xeon/Celeron (547.18-MHz 686-class CPU)
>disk :
>    1 x 9gig scsi
>
>im not using the -B option so i guess im using the3 default number/size
>buffers.
>
>hope this helps!
>
>thanks again for all your help a i am completely clueless!
>
>colm ennis
>
>-----Original Message-----
>From: Antonio Fiol Bonnin [mailto:fiol@w3ping.com]
>Sent: 13 December 2001 18:14
>To: colm ennis
>Cc: PostgreSQL General Mailing list
>Subject: Re: [GENERAL] slow queries on large syslog table
>
>
>Are the rows estimations "real"?
>
>
>colm ennis wrote:
>
>>hi antonio,
>>
>>thanks for your advice.
>>
>>ive tried a lot of different index combinations, with extremely variable
>>results,
>>for instance :
>>    query - SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid
IN
>>(23,3)) AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT
1000
>>    matching messages - 19
>>
>>with original indexes :
>>    query time(s) - 225
>>    explain - Limit  (cost=0.00..34559.46 rows=1000 width=24)
>> ->  Index Scan Backward using syslog_table_stimestamp_index on
>>syslog_table  (cost=0.00..577149.86 rows=16700 width=24)
>>
>>with antonios index :
>>    create index syslog_table_stimestamp_shostid_sciscomsgid_index on
>>syslog_table (stimestamp, shostid, sciscomsgid);
>>    query time(s) - 174
>>    explain - Limit  (cost=0.00..34329.14 rows=1000 width=24)
>> ->  Index Scan Backward using syslog_table_st_sh_sc_index on syslog_table
>>(cost=0.00..580639.57 rows=16914 width=24)
>>
>>with NO! index :
>>    query time(s) - 77
>>    explain - Limit  (cost=73979.79..73979.79 rows=1000 width=24)
>> ->  Sort  (cost=73979.79..73979.79 rows=16905 width=24)
>>       ->  Seq Scan on syslog_table  (cost=0.00..72591.62 rows=16905
>>width=24)
>>
>>i got similarily confusing results from other queries.
>>
>>it occured to me that that the index antonio suggests is going to be huge
>>because
>>of the per second timestamp.
>>
>>i thought about what you said about deciding how I would search for data.
>>
>>for the query above :
>>    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN
(23,3))
>>AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
>>i would lookup shostid and sciscomsgid in a combined index, and then load
>>the
>>indexed rows in syslog_table.  there are about 80 different shostids and
>>sciscomsgids, so i guess the max size of this index would be 1600 rows.
>>
>>for other queries i would use an shostid index or sciscomsgid if just one
>>
>of
>
>>these
>>fields appeared in the select, avoiding the stimestamp at all cost because
>>its index
>>will be huge.
>>
>>so to test if this was any good i created the combined index :
>>    create index syslog_table_sh_sc_index on syslog_table (shostid,
>>sciscomsgid);
>>but using explain found it isnt being used? :
>>    explain - Limit  (cost=74018.18..74018.18 rows=1000 width=24)
>> ->  Sort  (cost=74018.18..74018.18 rows=16914 width=24)
>>       ->  Seq Scan on syslog_table  (cost=0.00..72629.33 rows=16914
>>width=24)
>>
>>i still dont understand how to use indexes to increase the speed of
>>
>queries.
>
>>thanks for your help so far but i still feel lost,
>>
>>colm ennis
>>
>>-----Original Message-----
>>From: Antonio Fiol Bonnin [mailto:fiol@w3ping.com]
>>Sent: 13 December 2001 15:29
>>To: colm ennis; PostgreSQL General Mailing list
>>Subject: Re: [GENERAL] slow queries on large syslog table
>>
>>
>>>
>>>ive also created a web interface for selecting syslogs based on optional
>>>combinations of timestamp, hostname and ciscomsg.
>>>
>>Combinations is the *magic* word.
>>
>>>to speed queries i created some indexes on syslog_table :
>>>    create index syslog_table_stimestamp_index on syslog_table (stimestamp);
>>>    create index syslog_table_shostid_index on syslog_table (shostid);
>>>    create index syslog_table_sciscomsgid_index on syslog_table
>>>
>(sciscomsgid);
>
>>>    create index syslog_table_shostid_sciscomsgid_index on syslog_table
>>>(shostid,sciscomsgid);
>>>
>>Most of them are of no use. That's what you observed...
>>
>>I bet you will get much better perfs with:
>>
>>create index syslog_table_stimestamp_shostid_sciscomsg_index on
>>
>syslog_table
>
>>(stimestamt, shostid, sciscomsg);
>>
>>You can try other combinations, but the one I suggested should be of use
>>in case you use all three on the query, (or even if you use only the
>>first, or the first two, though not sure about this last part, in
>>parentheses).
>>
>>
>>Believe me. Creating an index on a large table is of no use, unless it
>>is the right one. As a rule of thumb, include in the index as many of
>>the SELECTIVE columns present in the WHERE clause as you can.
>>
>>I am not sure of the selectivity of your columns (never used that
>>particular structure). There should be some information about that on
>>some of the system tables. However, I do not know in which, or how to
>>get that info.
>>
>>As a second rule of thumb, think how YOU would search for the data you
>>need if it was written on a paper book, and especially, how you would
>>like to find the book ordered. For example, if you were to look the
>>address corresponding to a phone number, you would like to find the data
>>ordered by phone number, and not by name. Name is not of any use to you.
>>
>>For selectivity, think of finding the phone numbers of all people that
>>live at number 5, but of any street, and whose first name is Peter.
>>
>>Neither "Peter" nor "5" are REALLY useful informations to perform your
>>search. And even, Peter is more useful than 5.
>>
>>HTH,
>>
>>Antonio
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>
>>.
>>
>
>
>
>
>.
>