pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex problem
16+ messages / 9 participants
[nested] [flat]
* Index problem
@ 2000-11-15 16:21 Patrik Kudo <kudo@partitur.se>
2000-11-15 18:19 ` Re: Index problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Patrik Kudo @ 2000-11-15 16:21 UTC (permalink / raw)
To: pgsql-sql; +Cc: girgen@partitur.se
Hi!
We're using postgres 6.5.3 for an application where we, among other
things, store information about users. On two separate occations on
two different servers we've experienced problems with indices on the
same table (out of over 100).
The problem is that postgres refuse to find a certain row in the table
when performing a simple select using the primary (unique) key like
this:
select * from elever where userid = 'walther';
When selecting all rows the row in question is shown. When doing the
following, I get the result I want:
select * from elever where userid like 'w%'; <-- OK
select * from elever where userid like 'wa%'; <-- OK
select * from elever where userid like 'wal%'; <-- OK
select * from elever where userid like 'walt%'; <-- OK
but not
select * from elever where userid like 'walth%'; <-- Not OK!
Droping and recreating the index solves the problem, but that's not
good enough for me since the problem has reoccured on a different
machine with a different database. vacuum and vacuum analyze does not
report any problem with the table.
Both times the problem occured with userid's starting with a
"w". Postgres is running with a Swedish locale, and on FreeBSD this
means that "w" and "v" (among a number of other letter) are treated
equally when collating/sorting. I suppose this could be part of the
problem.
Is this a known problem? If so, is it fixed in 7.0.2? I've not seen
this happen on any of our postgres 7.0.2 systems, but as I can't even
reproduce it on the 6.5.3, that's no guarantee it's fixed...
For the record... I know we shouldn't run 6.5.3 and we will upgrade to
7.x within soon...
psql -c 'select version()'
PostgreSQL 6.5.3 on i386-unknown-freebsd4.1, compiled by cc
uname -a
FreeBSD 4.1-RELEASE #0: Tue Aug 29 15:31:01 CEST 2000
Regards,
Patrik Kudo
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Index problem
2000-11-15 16:21 Index problem Patrik Kudo <kudo@partitur.se>
@ 2000-11-15 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-15 22:33 ` Re: Index problem Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2000-11-15 18:19 UTC (permalink / raw)
To: Patrik Kudo <kudo@partitur.se>; +Cc: pgsql-sql@postgresql.org, girgen@partitur.se, Peter Eisentraut <peter_e@gmx.net>
Patrik Kudo <kudo@partitur.se> writes:
> select * from elever where userid like 'walth%'; <-- Not OK!
> Droping and recreating the index solves the problem, but that's not
> good enough for me since the problem has reoccured on a different
> machine with a different database. vacuum and vacuum analyze does not
> report any problem with the table.
> Both times the problem occured with userid's starting with a
> "w". Postgres is running with a Swedish locale, and on FreeBSD this
> means that "w" and "v" (among a number of other letter) are treated
> equally when collating/sorting. I suppose this could be part of the
> problem.
> Is this a known problem? If so, is it fixed in 7.0.2? I've not seen
> this happen on any of our postgres 7.0.2 systems, but as I can't even
> reproduce it on the 6.5.3, that's no guarantee it's fixed...
Hmm. I can think of two known issues that may be relevant. First off,
there are indeed known problems with LIKE index optimization in
non-ASCII locales. I believe that 7.0 fixes the problems for locales
that just have a non-ASCII sort order of individual characters, but
we've since realized that it can still do the wrong thing in locales
where there are special rules for handling multi-character sequences.
I don't know the Swedish rules offhand, so don't know if that's a
problem for you.
However, a LIKE optimization problem would not be fixed by dropping and
recreating the index. This makes me think you are being bit by the
other issue: if you compile with LOCALE support then it is critical that
the postmaster *always* be started with the same LOCALE settings (at
least the same LC_COLLATE value). If LOCALE varies that means your
text sort ordering varies, which means that indexes on text columns may
appear out-of-order, which causes index searches to miss entries they
should have found. If you insert entries under different LOCALE
settings then you may wind up with an index that is not consistent with
*any* single LOCALE; the only cure for that is to drop and rebuild the
index.
Unfortunately, it's way too easy to get bit by this bug. The most
common error is to start the postmaster by hand from a shell account
whose LOCALE environment is different from what's supplied when the
postmaster is started from a boot-time script. Best bet is to set
the correct LOCALE values in a wrapper script that you use in both
cases.
We have talked about fixing this by saving the active LOCALE variables
at initdb time, and having the postmaster adopt those values whenever
it's started. But it hasn't got done yet. (Peter, would it be
easy to make GUC handle this? There'd need to be some way to cause
guc.c to do a putenv() ...)
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Index problem
2000-11-15 16:21 Index problem Patrik Kudo <kudo@partitur.se>
2000-11-15 18:19 ` Re: Index problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-11-15 22:33 ` Peter Eisentraut <peter_e@gmx.net>
2000-11-16 03:54 ` Re: Index problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Peter Eisentraut @ 2000-11-15 22:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patrik Kudo <kudo@partitur.se>; pgsql-sql; girgen@partitur.se
Tom Lane writes:
> We have talked about fixing this by saving the active LOCALE variables
> at initdb time, and having the postmaster adopt those values whenever
> it's started. But it hasn't got done yet. (Peter, would it be
> easy to make GUC handle this? There'd need to be some way to cause
> guc.c to do a putenv() ...)
I thought pg_control was the place for this. Any idea how long a locale
string can legally be? Doesn't look too hard beyond that.
Then again, perhaps this should be a per-database thing, much like the
encoding. The postmaster doesn't do any collating, so it should be safe
to have different locale in different backends. -- Unless someone puts an
index on pg_database.datname. :-)
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Index problem
2000-11-15 16:21 Index problem Patrik Kudo <kudo@partitur.se>
2000-11-15 18:19 ` Re: Index problem Tom Lane <tgl@sss.pgh.pa.us>
2000-11-15 22:33 ` Re: Index problem Peter Eisentraut <peter_e@gmx.net>
@ 2000-11-16 03:54 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2000-11-16 03:54 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Patrik Kudo <kudo@partitur.se>; pgsql-sql; girgen@partitur.se
Peter Eisentraut <peter_e@gmx.net> writes:
>> But it hasn't got done yet. (Peter, would it be
>> easy to make GUC handle this? There'd need to be some way to cause
>> guc.c to do a putenv() ...)
> I thought pg_control was the place for this. Any idea how long a locale
> string can legally be? Doesn't look too hard beyond that.
That would work too, I guess.
> Then again, perhaps this should be a per-database thing, much like the
> encoding. The postmaster doesn't do any collating, so it should be safe
> to have different locale in different backends. -- Unless someone puts an
> index on pg_database.datname. :-)
There already is an index on pg_group.groname, so I think we'd better be
conservative and require the same locale across the whole installation.
Hmm ... doesn't that mean that per-database encoding is a bogus concept
too!?
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Index problem...
@ 2001-02-09 13:05 Kim Yunhan <spbear@hanmail.net>
0 siblings, 0 replies; 16+ messages in thread
From: Kim Yunhan @ 2001-02-09 13:05 UTC (permalink / raw)
To: pgsql-sql
I want to query this...
--> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
--> CREATE INDEX idx_bbs ON bbs (ref, step);
but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result very fast. :-(
so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index.
what do i do?
how make an index?
==================================================
==================================================
¿ì¸® ÀÎÅͳÝ, Daum
Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀϳÝ
Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL
http://www.daum.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* Index Problem
@ 2001-02-09 14:54 Kim Yunhan <spbear@hanmail.net>
2001-02-09 06:22 ` Re: Index Problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Kim Yunhan @ 2001-02-09 14:54 UTC (permalink / raw)
To: pgsql-sql
I want to query this...
--> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
--> CREATE INDEX idx_bbs ON bbs (ref, step);
but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result very fast. :-(
so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index.
what do i do?
how make an index?
==================================================
==================================================
¿ì¸® ÀÎÅͳÝ, Daum
Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀϳÝ
Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL
http://www.daum.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Index Problem
2001-02-09 14:54 Index Problem Kim Yunhan <spbear@hanmail.net>
@ 2001-02-09 06:22 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2001-02-09 06:22 UTC (permalink / raw)
To: Kim Yunhan <spbear@hanmail.net>; +Cc: pgsql-sql
"Kim Yunhan" <spbear@hanmail.net> writes:
> I want to query this...
> --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
> this query doesn't refer the index that made by this query.
> --> CREATE INDEX idx_bbs ON bbs (ref, step);
Well, no. The ordering the query is asking for has nothing to do with
the ordering of the index, so the index is no help.
The simplest answer would be to restructure your data so that the order
you are interested in corresponds to the natural index order. If you
don't like that answer, you could consider making a "reverse" operator
class that sorts the datatype of "ref" in reverse order, and then
building an index on (ref reverse_ops, step). I think the planner would
be smart enough to realize that it could use such an index for your
query ... but it's a sufficiently off-the-wall case that I doubt
anyone's ever tried it. Lemme know if it works ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Index Problem
@ 2001-02-09 15:06 Kim Yunhan <spbear@hanmail.net>
0 siblings, 0 replies; 16+ messages in thread
From: Kim Yunhan @ 2001-02-09 15:06 UTC (permalink / raw)
To: pgsql-sql
I want to query this...
--> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12;
this query doesn't refer the index that made by this query.
--> CREATE INDEX idx_bbs ON bbs (ref, step);
but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result very fast. :-(
so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index.
what do i do?
how make an index?
==================================================
==================================================
¿ì¸® ÀÎÅͳÝ, Daum
Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀϳÝ
Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL
http://www.daum.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* index problem
@ 2001-10-15 19:41 Szabo Zoltan <col@mportal.hu>
2001-10-16 15:57 ` Re: index problem Szabo Zoltan <col@mportal.hu>
2001-10-16 20:05 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 16+ messages in thread
From: Szabo Zoltan @ 2001-10-15 19:41 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have that:
1)
db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
NOTICE: QUERY PLAN:
Group (cost=0.00..29970.34 rows=921 width=4)
-> Index Scan using progdgyxy_idx2 on prog_dgy_xy
(cost=0.00..29947.32 rows=9210 width=4)
than:
2)
db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
NOTICE: QUERY PLAN:
Group (cost=66927.88..67695.39 rows=30700 width=4)
-> Sort (cost=66927.88..66927.88 rows=307004 width=4)
-> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004
width=4)
I making some banchmarks on: oracle vs postgres vs mysql. And this is
breaking me now;) Mysql and oracle width same table and index use that
index on pxygy_pid;
I had vacuum before.
Time with mysql:
bash-2.04$ time echo " select count(*) from PROG_DGY_XY where
pxygy_pid>12121;" | mysql -uuser -ppasswd db
count(*)
484984
real 0m13.761s
user 0m0.008s
sys 0m0.019s
Time with postgres:
bash-2.04$ time echo "select count(*) from PROG_DGY_XY where
pxygy_pid>12121 " | psql -Uuser db
count
--------
484984
(1 row)
real 0m22.480s
user 0m0.011s
sys 0m0.021s
And this is just a little part of another selects joining tables, but
because this index is not used, selecting from 2 tables (which has
indexes, and keys on joining collumns) takes extrem time for postgres:
2m14.978s while for mysql it takes: 0m0.578s !!!
this select is: select distinct
PROG_ID,PROG_FTYPE,PROG_FCASTHOUR,PROG_DATE from PROG_DATA, PROG_DGY_XY
where prog_id=pxygy_pid order by prog_date,prog_ftype,prog_fcasthour
indexes:
PROG_DATA:
create index prod_data_idx1 on prog_data
(prog_date,prog_ftype,prog_fcasthour);
prog_id is primary key
PROG_DGY_XY:
create unique index progdgyxy_idx1 on PROG_DGY_XY
(PXYGY_PID,PXYGY_X,PXYGY_Y);
create index progdgyxy_idx2 on PROG_DGY_XY (PXYGY_PID);
Thx
CoL
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: index problem
2001-10-15 19:41 index problem Szabo Zoltan <col@mportal.hu>
@ 2001-10-16 15:57 ` Szabo Zoltan <col@mportal.hu>
1 sibling, 0 replies; 16+ messages in thread
From: Szabo Zoltan @ 2001-10-16 15:57 UTC (permalink / raw)
To: pgsql-sql
I forget:
select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
It seems that there are index using problems in 7.1.3 ?
(checkin same problem in comp.databases.postgresql.bugs msg from Orion)
thx
CoL
Szabo Zoltan wrote:
> Hi,
>
> I have that:
>
> 1)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=0.00..29970.34 rows=921 width=4)
> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
> (cost=0.00..29947.32 rows=9210 width=4)
>
> than:
> 2)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=66927.88..67695.39 rows=30700 width=4)
> -> Sort (cost=66927.88..66927.88 rows=307004 width=4)
> -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004
> width=4)
>
> I making some banchmarks on: oracle vs postgres vs mysql. And this is
> breaking me now;) Mysql and oracle width same table and index use that
> index on pxygy_pid;
> I had vacuum before.
>
> Time with mysql:
>
> bash-2.04$ time echo " select count(*) from PROG_DGY_XY where
> pxygy_pid>12121;" | mysql -uuser -ppasswd db
> count(*)
> 484984
>
> real 0m13.761s
> user 0m0.008s
> sys 0m0.019s
>
> Time with postgres:
> bash-2.04$ time echo "select count(*) from PROG_DGY_XY where
> pxygy_pid>12121 " | psql -Uuser db
> count
> --------
> 484984
> (1 row)
>
>
> real 0m22.480s
> user 0m0.011s
> sys 0m0.021s
>
> And this is just a little part of another selects joining tables, but
> because this index is not used, selecting from 2 tables (which has
> indexes, and keys on joining collumns) takes extrem time for postgres:
> 2m14.978s while for mysql it takes: 0m0.578s !!!
>
> this select is: select distinct
> PROG_ID,PROG_FTYPE,PROG_FCASTHOUR,PROG_DATE from PROG_DATA, PROG_DGY_XY
> where prog_id=pxygy_pid order by prog_date,prog_ftype,prog_fcasthour
>
> indexes:
> PROG_DATA:
> create index prod_data_idx1 on prog_data
> (prog_date,prog_ftype,prog_fcasthour);
> prog_id is primary key
>
> PROG_DGY_XY:
> create unique index progdgyxy_idx1 on PROG_DGY_XY
> (PXYGY_PID,PXYGY_X,PXYGY_Y);
> create index progdgyxy_idx2 on PROG_DGY_XY (PXYGY_PID);
>
>
> Thx
> CoL
>
--
[ Szabo Zoltan ]
[ software fejleszto ]
[ econet.hu Informatikai Rt. ]
[ 1117 Budapest, Hauszmann A. u. 3. ]
[ tel.: 371 2100 fax: 371 2101 ]
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: index problem
2001-10-15 19:41 index problem Szabo Zoltan <col@mportal.hu>
@ 2001-10-16 20:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-16 21:08 ` Re: index problem CoL <col@mportal.hu>
1 sibling, 1 reply; 16+ messages in thread
From: Stephan Szabo @ 2001-10-16 20:05 UTC (permalink / raw)
To: Szabo Zoltan <col@mportal.hu>; +Cc: pgsql-sql
On Mon, 15 Oct 2001, Szabo Zoltan wrote:
> Hi,
>
> I have that:
>
> 1)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=0.00..29970.34 rows=921 width=4)
> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
> (cost=0.00..29947.32 rows=9210 width=4)
>
> than:
> 2)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
> NOTICE: QUERY PLAN:
>
> Group (cost=66927.88..67695.39 rows=30700 width=4)
> -> Sort (cost=66927.88..66927.88 rows=307004 width=4)
> -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004
> width=4)
>
> I making some banchmarks on: oracle vs postgres vs mysql. And this is
> breaking me now;) Mysql and oracle width same table and index use that
> index on pxygy_pid;
> I had vacuum before.
I assume you mean you did a vacuum analyze (a plain vacuum isn't
sufficient). If you did just do a regular vacuum, do a vacuum analyze
to get the updated statistics.
How many rows actually match pxygy_pid>12121? Is 307000 rows a reasonable
estimate? How many rows are in the table?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: index problem
2001-10-15 19:41 index problem Szabo Zoltan <col@mportal.hu>
2001-10-16 20:05 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-10-16 21:08 ` CoL <col@mportal.hu>
2001-10-16 22:56 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 16+ messages in thread
From: CoL @ 2001-10-16 21:08 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi,
I did not make vacuum analyze ;), the vacuum once now:
vacuumdb -Uuser -ddb -v -tprog_dgy_xy
NOTICE: --Relation prog_dgy_xy--
NOTICE: Pages 20935: Changed 0, reaped 0, Empty 0, New 0; Tup 921013:
Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 178, MaxLen 184;
Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
2.71s/0.32u sec.
NOTICE: Index progdgyxy_idx1: Pages 6679; Tuples 921013. CPU
1.41s/1.40u sec.
NOTICE: Index progdgyxy_idx2: Pages 2019; Tuples 921013. CPU
0.28s/1.28u sec.
I make it with -z too.
So this table has more 921013 rows.
The query show the same as bellow. The version is 7.1.3.
-------------------------
One more interesting: the insering of these rows.
Postgres:
bash-2.04$ time psql -q -Uuser -f prog_dgy_xy.dump db
real 131m50.006s
user 3m21.838s
sys 1m20.963s
Mysql:
bash-2.04$ time cat prog_dgy_xy.dump | mysql -uuser -ppass db
real 24m50.137s
user 2m6.629s
sys 1m37.757s
the dump file was: insert into table (...) values (...);
I tried with copy, and to add begin; inserts; commit; , but the result
with same time :(
[For Oracle 8.1.6 sqlloader it takes 450 sec ;) ]
---------------------------
The 2 table query, where prog_data has ~8800 rowsn and index on prog_id:
bash-2.04$ time echo "explain select distinct
prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data
where pxygy_pid=prog_id " | psql -Uuser db
NOTICE: QUERY PLAN:
Unique (cost=7432549.69..7680455.07 rows=2479054 width=32)
-> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32)
-> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32)
-> Index Scan using prog_data_pkey on prog_data
(cost=0.00..701.12 rows=8872 width=28)
-> Sort (cost=148864.65..148864.65 rows=921013 width=4)
-> Seq Scan on prog_dgy_xy (cost=0.00..30145.13
rows=921013 width=4)
Time: !!!
real 2m3.620s
the same query with mysql (i did explain in mysql, and says it use the
indexes):
real 0m1.998s !!!
I just askin why? and why just using the index on releation "=".
(same table, same index, vacuumed) (made the test more than twice)
It seams to be a 7.1.3 bug? i do not test yet with 7.1.2 but tomorrow i
will.
CoL
Stephan Szabo wrote:
> On Mon, 15 Oct 2001, Szabo Zoltan wrote:
>
>
>>Hi,
>>
>>I have that:
>>
>>1)
>>db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
>>NOTICE: QUERY PLAN:
>>
>>Group (cost=0.00..29970.34 rows=921 width=4)
>> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
>>(cost=0.00..29947.32 rows=9210 width=4)
>>
>>than:
>>2)
>>db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
>>NOTICE: QUERY PLAN:
>>
>>Group (cost=66927.88..67695.39 rows=30700 width=4)
>> -> Sort (cost=66927.88..66927.88 rows=307004 width=4)
>> -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004
>>width=4)
>>
>>I making some banchmarks on: oracle vs postgres vs mysql. And this is
>>breaking me now;) Mysql and oracle width same table and index use that
>>index on pxygy_pid;
>>I had vacuum before.
>>
>
> I assume you mean you did a vacuum analyze (a plain vacuum isn't
> sufficient). If you did just do a regular vacuum, do a vacuum analyze
> to get the updated statistics.
>
> How many rows actually match pxygy_pid>12121? Is 307000 rows a reasonable
> estimate? How many rows are in the table?
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: index problem
2001-10-15 19:41 index problem Szabo Zoltan <col@mportal.hu>
2001-10-16 20:05 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-16 21:08 ` Re: index problem CoL <col@mportal.hu>
@ 2001-10-16 22:56 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 16+ messages in thread
From: Stephan Szabo @ 2001-10-16 22:56 UTC (permalink / raw)
To: CoL <col@mportal.hu>; +Cc: pgsql-sql
On Tue, 16 Oct 2001, CoL wrote:
> ---------------------------
> The 2 table query, where prog_data has ~8800 rowsn and index on prog_id:
> bash-2.04$ time echo "explain select distinct
> prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data
> where pxygy_pid=prog_id " | psql -Uuser db
> NOTICE: QUERY PLAN:
>
> Unique (cost=7432549.69..7680455.07 rows=2479054 width=32)
> -> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32)
> -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32)
> -> Index Scan using prog_data_pkey on prog_data
> (cost=0.00..701.12 rows=8872 width=28)
> -> Sort (cost=148864.65..148864.65 rows=921013 width=4)
> -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13
> rows=921013 width=4)
I'm guessing that the approximately 25 million row estimate on the join
has to be wrong as well given that prog_data.prog_id should be unique.
Hmm, does the explain change if you vacuum analyze the other table
(prog_data)? If not, what does explain show if you do a
set enable_seqscan='off';
before it?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: index problem
@ 2001-10-17 17:47 Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 16+ messages in thread
From: Stephan Szabo @ 2001-10-17 17:47 UTC (permalink / raw)
To: CoL <col@mportal.hu>; +Cc: pgsql-sql
> > Hmm, does the explain change if you vacuum analyze the other table
> > (prog_data)? If not, what does explain show if you do a
> > set enable_seqscan='off';
> > before it?
Did you do the vacuum analyze on the other table (prog_data) as well?
It seems to be overestimating the number of joined rows, and I wonder
if it would choose a different plan if it had the correct number.
> The result:
> db=>set enable_seqscan='off';
> db=>explain select distinct prog_id,prog_ftype,prog_fcasthour,prog_date
> from prog_dgy_xy,prog_data where pxygy_pid=prog_id;
> NOTICE: QUERY PLAN:
>
> Unique (cost=7606982.10..7854887.48 rows=2479054 width=32)
> -> Sort (cost=7606982.10..7606982.10 rows=24790538 width=32)
> -> Merge Join (cost=0.00..335621.73 rows=24790538 width=32)
> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
> (cost=0.00..323297.05 rows=921013 width=4)
> -> Index Scan using prog_data_pkey on prog_data
> (cost=0.00..701.12 rows=8872 width=28)
>
> It "seems" index is used, but the same result :(((, and bigger execution
> time: real 3m41.830s
Well, that means the plan it chose before was better, so enable_seqscan
isn't a win here.
> And why:
> POSTGRES:
> set enable_seqscan ='off'; select count(*) from prog_dgy_xy where
> pxygy_pid<13161;
> count
> --------
> 900029
> real 2m34.340s
> explain:
> Aggregate (cost=327896.89..327896.89 rows=1 width=0)
> -> Index Scan using progdgyxy_idx2 on prog_dgy_xy
> (cost=0.00..325594.54 rows=920940 width=0)
It's estimating the entire table will be seen (or most of it anyway),
so it would choose Seq Scan as faster, but you've basically disallowed
that with the enable_seqscan='off'. Is it faster without the explicit
hint (it probably will be). Index Scans are not always better than
Sequence Scans (especially when traversing most of the table as in the
above) and you don't want to use the enable_* unless it actually
is giving you a performance increase.
^ permalink raw reply [nested|flat] 16+ messages in thread
* index problem
@ 2002-06-21 18:59 Oleg Lebedev <oleg.lebedev@waterford.org>
0 siblings, 0 replies; 16+ messages in thread
From: Oleg Lebedev @ 2002-06-21 18:59 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have an objectid field of type bigint, but when I run queries like:
select * from table where objectid=123;
index is not used on objectid even though it's declared.
However when I run:
select * from table where objectid='123';
index is used.
Is there an option I can set so that postgres starts using indexes on
numberic field without requiring
conversion to varchar?
thanks,
Oleg
^ permalink raw reply [nested|flat] 16+ messages in thread
* INDEX problem
@ 2004-09-24 14:21 Vladimir S. Tikhonjuk <vst@vst.donetsk.ua>
0 siblings, 0 replies; 16+ messages in thread
From: Vladimir S. Tikhonjuk @ 2004-09-24 14:21 UTC (permalink / raw)
To: pgsql-sql
Hi all!
I have such table:
CREATE TABLE object (
id SERIAL,
object_type_id int8
);
This table has 4 000 000 rows.
There are 2 index:
CREATE INDEX object_id_idx ON object(id);
CREATE INDEX object_object_type_id_idx ON object(object_type_id);
So:
# EXPLAIN SELECT * FROM object WHERE id = 1::int8;
Index Scan using object_id_idx on object (cost=0.00..92323.66
rows=23650 width=29)
Index Cond: (id = 1::bigint)
Here everything is O.K.
# EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8;
Index Scan using object_object_type_id_idx on object
(cost=0.00..92323.66 rows=23650 width=29)
Index Cond: (object_type_id = 1::bigint)
Here everything is O.K. too... but!
# EXPLAIN SELECT * FROM object WHERE object_type_id IN (1::int8, 21::int8);
Seq Scan on object (cost=0.00..105730.00 rows=47182 width=29)
Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint))
The same results after:
# EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8 OR
object_type_id = 21::int8;
Why Postgres didn't use index here ???
# EXPLAIN ANALYZE SELECT * FROM object WHERE object_type_id IN (1::int8,
21::int8);
Seq Scan on object (cost=0.00..105730.00 rows=47182 width=29) (actual
time=20744.910..20744.910 rows=0 loops=1)
Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint))
Total runtime: 20745.022 ms
Best regards,
Vladimir S. Tikhonjuk
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2004-09-24 14:21 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-15 16:21 Index problem Patrik Kudo <kudo@partitur.se>
2000-11-15 18:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-15 22:33 ` Peter Eisentraut <peter_e@gmx.net>
2000-11-16 03:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-09 13:05 Index problem... Kim Yunhan <spbear@hanmail.net>
2001-02-09 14:54 Index Problem Kim Yunhan <spbear@hanmail.net>
2001-02-09 06:22 ` Re: Index Problem Tom Lane <tgl@sss.pgh.pa.us>
2001-02-09 15:06 Index Problem Kim Yunhan <spbear@hanmail.net>
2001-10-15 19:41 index problem Szabo Zoltan <col@mportal.hu>
2001-10-16 15:57 ` Re: index problem Szabo Zoltan <col@mportal.hu>
2001-10-16 20:05 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-16 21:08 ` Re: index problem CoL <col@mportal.hu>
2001-10-16 22:56 ` Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-17 17:47 Re: index problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-06-21 18:59 index problem Oleg Lebedev <oleg.lebedev@waterford.org>
2004-09-24 14:21 INDEX problem Vladimir S. Tikhonjuk <vst@vst.donetsk.ua>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox