Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9I1qmP32251 for ; Wed, 17 Oct 2001 21:52:48 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 484E5242F; Wed, 17 Oct 2001 21:52:22 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3BCE35D6.00000B.93192@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: Re: Indexes on functions From: "Michael Richards" X-Fastmail-IP: 24.43.130.241 Date: Wed, 17 Oct 2001 21:52:22 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200110/248 X-Sequence-Number: 5203 I'm going to write a function in C that parses XML. Is there any way to index the output of this function? I've got 10,000,000 rows that contain XML data and I need to efficiently find the ones that contain the proper keys. I tried pulling the values I want from the data and putting it in its own table but with an average of 20 items that just grows to an unmanageable size. -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Free Web Email for Canadians From pgsql-sql-owner@postgresql.org Thu Oct 18 09:40:33 2001 Received: from fw-ext.econet.hu (fw.econet.hu [213.16.96.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HENGP37202 for ; Wed, 17 Oct 2001 10:23:16 -0400 (EDT) (envelope-from col@mportal.hu) Received: from mail.econet.hu ([172.17.1.17]) by fw-ext.econet.hu with esmtp (Exim 3.12 #1 (Debian)) id 15trgk-000242-00; Wed, 17 Oct 2001 16:28:54 +0200 Received: from host152.localnet3 ([192.168.12.152] helo=mportal.hu) by mail.econet.hu with esmtp (Exim 3.12 #1 (Debian)) id 15trb4-0003hG-00; Wed, 17 Oct 2001 16:23:02 +0200 Message-ID: <3BCD9582.1070804@mportal.hu> Date: Wed, 17 Oct 2001 16:28:18 +0200 From: CoL User-Agent: Mozilla/5.0 (Windows; U; Win95; en-US; rv:0.9.2) Gecko/20010726 Netscape6/6.1 X-Accept-Language: en-us MIME-Version: 1.0 To: Stephan Szabo CC: pgsql-sql@postgresql.org Subject: Re: index problem References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Scanner: exiscan *15trb4-0003hG-00*YiTaXybQ75Y* http://duncanthrax.net/exiscan/ X-Archive-Number: 200110/249 X-Sequence-Number: 5204 Hi, Stephan Szabo wrote: > On Tue, 16 Oct 2001, CoL wrote: > > >>--------------------------- >>The 2 table query, where prog_data has ~8800 rowsn and index on prog_id: >>bash-2.04$ time echo "explain select distinct >>prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data >>where pxygy_pid=prog_id " | psql -Uuser db >>NOTICE: QUERY PLAN: >> >>Unique (cost=7432549.69..7680455.07 rows=2479054 width=32) >> -> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32) >> -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32) >> -> Index Scan using prog_data_pkey on prog_data >>(cost=0.00..701.12 rows=8872 width=28) >> -> Sort (cost=148864.65..148864.65 rows=921013 width=4) >> -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13 >>rows=921013 width=4) >> > > I'm guessing that the approximately 25 million row estimate on the join > has to be wrong as well given that prog_data.prog_id should be unique. > > Hmm, does the explain change if you vacuum analyze the other table > (prog_data)? If not, what does explain show if you do a > set enable_seqscan='off'; > before it? The result: db=>set enable_seqscan='off'; db=>explain select distinct prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data where pxygy_pid=prog_id; NOTICE: QUERY PLAN: Unique (cost=7606982.10..7854887.48 rows=2479054 width=32) -> Sort (cost=7606982.10..7606982.10 rows=24790538 width=32) -> Merge Join (cost=0.00..335621.73 rows=24790538 width=32) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..323297.05 rows=921013 width=4) -> Index Scan using prog_data_pkey on prog_data (cost=0.00..701.12 rows=8872 width=28) It "seems" index is used, but the same result :(((, and bigger execution time: real 3m41.830s What is in tables? prog_data contains unique id and other info. prog_dgy_xy contains that id with x,y coordinates (so many ids from prog_data with unique x,y) #prog_data: #prog_id, prog_ftype, prog_fcasthour, prog_date #1 'type' 6 2001-10-14 12:00:00 #2 'type' 12 2001-10-14 12:00:00 #prog_dgy_xy: #pxygy_pid, pxygy_x, pxygy_y #1 0.1 0.1 #1 0.1 0.15 How can this query takes real 0m1.755s for mysql, [17 sec for oracle], and 2-3 minutes!! for postgres? And why: POSTGRES: set enable_seqscan ='off'; select count(*) from prog_dgy_xy where pxygy_pid<13161; count -------- 900029 real 2m34.340s explain: Aggregate (cost=327896.89..327896.89 rows=1 width=0) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..325594.54 rows=920940 width=0) MYSQL: select count(pxygy_pid) from PROG_DGY_XY where pxygy_pid<13161 count(pxygy_pid) 900029 real 0m27.878s explain: table type possible_keys key key_len ref rows Extra PROG_DGY_XY range progdgyxy_idx1,progdgyxy_idx2 progdgyxy_idx2 4 NULL 906856 where used; Using index The same time difference in case of: = or >, however explain says, cause seq scan is off, the index is used. I did vacuum, and vacuum analyze too before. PS: I think i have to make a site for that, cause there are many questions :), and weird things. I love postgres but this makes me "hm?". Today i'll make these test under 7.1.2. thx CoL