From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
Cc: | 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 15:47:37 |
Message-ID: | 570.976290457@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Barnett | 2000-12-08 16:03:18 | Memory Usage |
Previous Message | Jonathan Ellis | 2000-12-08 15:46:19 | Re: Oracle-compatible lpad/rpad behavior |
From | Date | Subject | |
---|---|---|---|
Next Message | mlw | 2000-12-08 16:01:11 | OK, does anyone have any better ideas? |
Previous Message | Jonathan Ellis | 2000-12-08 15:46:19 | Re: Oracle-compatible lpad/rpad behavior |