Re: Indexing for geographic objects?
От | Oleg Bartunov |
---|---|
Тема | Re: Indexing for geographic objects? |
Дата | |
Msg-id | Pine.GSO.3.96.SK.1001208203659.4174X-100000@ra обсуждение исходный текст |
Ответ на | Re: Indexing for geographic objects? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Indexing for geographic objects?
|
Список | pgsql-hackers |
On Fri, 8 Dec 2000, Tom Lane wrote: > Date: Fri, 08 Dec 2000 10:47:37 -0500 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: selkovjr@mcs.anl.gov, 'pgsql-hackers ' <pgsql-hackers@postgresql.org> > Subject: Re: [HACKERS] Indexing for geographic objects? > > Oleg Bartunov <oleg@sai.msu.su> writes: > > We've done some work with GiST indices and found a little problem > > with optimizer. > > > test=# set enable_seqscan = off; > > SET VARIABLE > > test=# explain select * from test where s @ '1.05 .. 3.95'; > > NOTICE: QUERY PLAN: > > > Index Scan using test_seg_ix on test (cost=0.00..369.42 rows=5000 width=12) > > > EXPLAIN > > % ./bench.pl -d test -b 100 -i > > total: 1.71 sec; number: 100; for one: 0.017 sec; found 18 docs > > I'd venture that the major problem here is bogus estimated selectivities > for rtree/gist operators. Note the discrepancy between the estimated > row count and the actual (I assume the "found 18 docs" is the true > number of rows output by the query). With an estimated row count even yes, 18 docs is the true number > half that (ie, merely two orders of magnitude away from reality ;-)) > the thing would've correctly chosen the index scan over sequential. > > 5000 looks like a suspiciously round number ... how many rows are in > the table? Have you done a vacuum analyze on it? park-lane:~/app/pgsql/gist_problem$ wc SQL 10009 10049 157987 SQL about 10,000 rows, relevant part of script is: .....skipped... 1.9039...3.5139 1.8716...3.9317 \. CREATE INDEX test_seg_ix ON test USING gist (s); vacuum analyze; ^^^^^^^^^^^^^^ explain select * from test where s @ '1.05 .. 3.95'; set enable_seqscan = off; explain select * from test where s @ '1.05 .. 3.95'; Regards, Oleg > > regards, tom lane > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
В списке pgsql-hackers по дате отправления: