Hi,
I'm using PG 8.0.3 and recently spotted a query that was not using a
multi-column index I had created.
The index looks like:
CREATE INDEX . ON FooTable(fkColumnOne, fkColumnTwo);
The query that was not using the index was using:
SELECT a bunch of columns and joins WHERE F
Hi, Otis,
[EMAIL PROTECTED] wrote:
> I'm using PG 8.0.3 and recently spotted a query that was not using a
> multi-column index I had created. The index looks like:
>
> CREATE INDEX . ON FooTable(fkColumnOne, fkColumnTwo);
>
> The query that was not using the index was using:
>
> SELECT ...
Hello,
I have a little 2-table JOIN, GROUP BY, ORDER BY query that does a sequential
scan on a multi-million row table. I _thought_ I had all the appropriate
indices, but apparently I do not. I was wondering if anyone can spot a way I
can speed up this query.
The query currently takes... *g
Markus Schaber <[EMAIL PROTECTED]> schrieb:
> Bitmap Index Scans can be your solution, but AFAIK they were invented in
> 8.1.
Right.
>
> For bitmap index scans, you have one index on fkColumnOne and one on
> fkColumnTwo, and the query planner knows to combine them when both
> columns are given i
On Wed, May 10, 2006 at 13:13:59 -0500,
[EMAIL PROTECTED] wrote:
> Hello,
>
> I have a little 2-table JOIN, GROUP BY, ORDER BY query that does a sequential
> scan on a multi-million row table. I _thought_ I had all the appropriate
> indices, but apparently I do not. I was wondering if anyo
Hi,
Thanks Bruno. That was indeed a redundant DISTINCT. It did reduce the amount
of work, but as you said it doesn't get rid of the sequential scan, which is
the real problem with this query.
Otis
- Original Message
From: Bruno Wolff III <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc:
<[EMAIL PROTECTED]> writes:
>-> Hash Join (cost=2797.65..140758.50 rows=3790 width=10)
> (actual time=248.530..380635.132 rows=8544 loops=1)
> Hash Cond: ("outer".user_url_id = "inner".id)
> -> Seq Scan on user_url_tag userurltag0_
Aha! set hashjoin=off did the trick.
The PG version is: 8.0.3
NB: I removed that redundant "DISTINCT" after the SELECT.
EXPLAIN ANALYZE select userurltag0_.tag as x0_0_, COUNT(*) as x1_0_ from
user_url_tag userurltag0_, user_url userurl1_ where (((userurl1_.user_id=1
))AND((userurltag0_.user_u
<[EMAIL PROTECTED]> writes:
> Aha! set hashjoin=off did the trick.
>-> Index Scan using ix_user_url_tag_user_url_id on
> user_url_tag userurltag0_ (cost=0.00..157.34 rows=103 width=14) (actual
> time=1.223..1.281 rows=5 loops=1666)
> Index Cond: (userurlta
Not sure if I'm showing you what you asked for, but here it is:
select * from pg_stats where tablename='user_url_tag' and
attname='user_url_id';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
Hi,
For example I have a table like this
ID Name
1 A
1 B
2 C
2 D
And I have a Query like this:
SELECT * FROM table ORDER BY ID.
Would my result always give me the same order or is there may be a possible
different result?
For example?
1 B
1 A
2 D
On May 11, 2006, at 21:00 , Christian Paul Cosinas wrote:
For example I have a table like this
ID Name
1 A
1 B
2 C
2 D
And I have a Query like this:
SELECT * FROM table ORDER BY ID.
Would my result always give me the same order or is there may be a
possible
di
12 matches
Mail list logo
