hello all,
I'm having troubles getting the following statement to use the index on
'gazette'. If i remove the order by condition it then uses the index. Below is
the explain analyse. The first explain analyse is the one i'm having problems
with. The second explain analyse is from a different d
Thanks, that was the problem: postgres thought it had to typecast the
column to a box type, which prevented use of the index.
For any PostGIS users reading this: the solution is to express the
other operand using a GeometryFromText(...) construct.
Thanks again,
-Greg
On May 5, 2007, at 7:4
=?ISO-8859-1?Q?Greg_Jan=E9e?= <[EMAIL PROTECTED]> writes:
> db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
> '((-120.1, 34.3), (-119.7, 34.4))' ;
> QUERY PLAN
> --
4 maj 2007 kl. 18:09 skrev Tom Lane:
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
I have a SELECT query that uses Seq scans instead of index scan
despite that the index scan is faster.
Try 8.2, it's a bit smarter about the costs of repeated indexscans
on the inside of a nestloop.
Ahh sorr
Hi, Postgres is refusing to use a GIST index on a spatial column.
Here's the table and column and index:
Table "public.scene"
Column| Type | Modifiers
-+-+---
...
footprint | ge
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
> I have a SELECT query that uses Seq scans instead of index scan
> despite that the index scan is faster.
Try 8.2, it's a bit smarter about the costs of repeated indexscans
on the inside of a nestloop.
regards, tom lane
---
Hello list,
I have a SELECT query that uses Seq scans instead of index scan
despite that the index scan is faster.
Below is the query and its first run with enable seqsan = true which
give a Seq Scan on tbl_structure (cost=0.00..19147.29 rows=172229
width=97) (actual time=0.094..878.309
On Fri, Mar 23, 2007 at 02:13:57PM +0100, Henrik Zagerholm wrote:
> I'm just amazed that setting the statistics threshold on one column
> mad all the difference. IS there any guidelines on what columns I
> should change the statistics on?
Start by looking for columns involved in simple compari
Never mind my last post regarding the Bitmap heap scan. That is all
in order.
Now it works perfectly. Went from 30+ seconds to 179ms. =)
I'm just amazed that setting the statistics threshold on one column
mad all the difference. IS there any guidelines on what columns I
should change the s
23 mar 2007 kl. 13:34 skrev Michael Fuhr:
On Fri, Mar 23, 2007 at 12:41:58PM +0100, Henrik Zagerholm wrote:
23 mar 2007 kl. 12:33 skrev Michael Fuhr:
The row count estimate for fk_filetype_id = 83 is high by an order
of magnitude:
Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1
Henrik Zagerholm wrote:
>> Have you run ANALYZE or VACUUM ANALYZE on these tables recently?
>> If so then you might try increasing the statistics target for
>> tbl_file.fk_filetype_id and perhaps some of the columns in the join
>> conditions.
>
> I did a vacuum full and reindex on all tables.
>
>
On Fri, Mar 23, 2007 at 12:41:58PM +0100, Henrik Zagerholm wrote:
> 23 mar 2007 kl. 12:33 skrev Michael Fuhr:
> >The row count estimate for fk_filetype_id = 83 is high by an order
> >of magnitude:
> >
> >>Bitmap Index Scan on tbl_file_idx6 (cost=0.00..25.65 rows=1251
> >>width=0) (actual time=21.9
23 mar 2007 kl. 12:33 skrev Michael Fuhr:
On Fri, Mar 23, 2007 at 11:43:54AM +0100, Henrik Zagerholm wrote:
I'm having a hard time understanding why my query is not using my
indices when I change a field in the WHERE clause.
The row count estimate for fk_filetype_id = 83 is high by an order
On Fri, Mar 23, 2007 at 11:43:54AM +0100, Henrik Zagerholm wrote:
> I'm having a hard time understanding why my query is not using my
> indices when I change a field in the WHERE clause.
The row count estimate for fk_filetype_id = 83 is high by an order
of magnitude:
> Bitmap Index Scan on tbl_
Hello list,
I'm having a hard time understanding why my query is not using my
indices when I change a field in the WHERE clause.
Please could someone shed some light on this.
Below is both queries and explain analyze for the different queries.
Regards,
Henrik
QUERY 1. Using WHERE tbl_file_s
Norbert Zoltan Toth writes:
> I have created the following test table:
> create table history (event_id int8 not null, event_type int, event_name
> char(10), event_ts timestamp, user_data_ref int8);
> When I issue the following query, it takes about 6 minutes:
> select event_id from hist
> explain select event_id from history where event_type = 120;
> NOTICE: QUERY PLAN:
>
> Seq Scan on history (cost=0.00..446079.00 rows=20 width=8)
>
> even if I do a drop index history_idx, explains sais the same.
>
Try
explain select event_id from history where event_ty
Hi,
I have created the following test table:
create table history (event_id int8 not null, event_type int, event_name
char(10), event_ts timestamp, user_data_ref int8);
I've populated the table with 20,000,000 rows.
I have created an index table:
create index history_idx on history (eve
18 matches
Mail list logo
