From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, selkovjr(at)mcs(dot)anl(dot)gov, "'pgsql-hackers '" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Indexing for geographic objects? |
Date: | 2000-12-08 16:19:56 |
Message-ID: | Pine.BSF.4.21.0012081218510.446-100000@thelab.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
just a note here ... recently, we had a client with similar problems with
using index scan, where turning off seqscan did the trick ... we took his
tables, loaded them into a v7.1beta1 server and it correctly comes up with
the index scan ...
Oleg, have you tried this with v7.1 yet?
On Fri, 8 Dec 2000, Tom Lane wrote:
> Oleg Bartunov <oleg(at)sai(dot)msu(dot)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
> 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?
>
> regards, tom lane
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
From | Date | Subject | |
---|---|---|---|
Next Message | Lamar Owen | 2000-12-08 16:36:56 | Re: Problems with Mandrake RPM |
Previous Message | Tom Lane | 2000-12-08 16:07:51 | Re: Memory Usage |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-12-08 16:49:00 | Re: RFC: CRC datatype |
Previous Message | Hannu Krosing | 2000-12-08 16:07:07 | Re: pre-beta is slow |