from:"Orion"

[BUGS] Cant get planner to use index (7.1.3-1PGDG)

2001-10-15 Thread Orion


Something strange is going on.  Postgres keeps wanting to do a sequential 
scan of my table when it REALLY should be using it's indexes.

I am running postgresql-7.1.3-1PGDG on RedHat 6.2 and on RedHat 7.0.

I have 300,000 records in this table and yes, I have vacuum analyzed.

Here is my table:


fdb=> \d mfps_orderinfo_435
Table "mfps_orderinfo_435"
  Attribute  |  Type   | Modifier
-+-+--
 order_number| integer | not null
 source_code | text|
 last_name   | text|
 first_name  | text|
 title   | text|
 address1| text|
 address2| text|
 city| text|
 state   | text|
 zip | text|
 telephone   | text|
 bill_method | text|
 cc  | text|
 exp | text|
 cc_auth_code| text|
 multi_billing_code  | text|
 order_header_status | text|
 order_date  | date|
 ship_date   | date|
 total_quantity  | integer |
 order_extension | money   |
 sales_tax   | money   |
 shipping| money   |
 total_discount  | money   |
 return_quantity | integer |
 return_amount   | money   |
 num_billings| integer |
 tracking_no1| text|
 tracking_no2| text|
 tracking_no3| text|
 email   | text|
 amount_paid | money   |
Indices: idx_mfps_orderinfo_435_odate,
 idx_mfps_orderinfo_435_fname,
 idx_mfps_orderinfo_435_lname,
 mfps_orderinfo_435_pkey


And here are two relevant indexes:


fdb=> \d idx_mfps_orderinfo_435_odate
Index "idx_mfps_orderinfo_435_odate"
 Attribute  | Type
+--
 order_date | date
btree

fdb=> \d idx_mfps_orderinfo_435_fname
Index "idx_mfps_orderinfo_435_fname"
 Attribute  | Type
+--
 first_name | text
btree


Now, Here's where things get weird.


fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date = 
current_date;
NOTICE:  QUERY PLAN:

Aggregate  (cost=13532.12..13532.12 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..13528.77 rows=1340 
width=0)

EXPLAIN


Here it does a straight date compare and it chooses not to use the index.  
What??


fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name = 
'SMITH';
NOTICE:  QUERY PLAN:

Aggregate  (cost=1044.16..1044.16 rows=1 width=0)
  ->  Index Scan using idx_mfps_orderinfo_435_fname on mfps_orderinfo_435  
(cost=0.00..1043.47 rows=279 width=0)

EXPLAIN
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name 
like 'SMITH%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=12769.48..12769.48 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=1 width=0)

EXPLAIN
fdb=>  explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name 
like 'SMITH';
NOTICE:  QUERY PLAN:

Aggregate  (cost=12770.17..12770.17 rows=1 width=0)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..12769.48 rows=279 width=0)

EXPLAIN


Here it will do an index scan if and only if I use the '=' operator.  If I 
use like with the % at the end of the string or EVEN if I have no wild card 
at all... it still does a seq scan.   If anyone has any advice on how to 
get these indexes working properly, please let me know.

Orion Henry





---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[BUGS] Index Scans Oddness

2001-10-15 Thread Orion




I posted two things I thought might be bugs about an hour ago with 
postgresql-7.1.3-1PGDG.  I just ran a regression test with postgresql-7.0.3 
and one problem vanished and one stayed.

(yes I vacuum analyzed)

1) the 

select * from foo where text_field like 'foo%'

will do an index scan with my data in 7.0.3 but not 7.1.3-1PGDG.
Makes me think there's a bug.

2) the

select * from foo where order_date = current_date
and
select * from foo
where order_date < to_date(xxx) and order_date > to_date(yyy) 

Both do sequence scans where current date is indexed on both 7.0.3 and 
7.1.3.  This leaves me quite confused since there are 300,000 rows and a 
sequential scan is doomed to take 15 to 20 seconds.  The few queries that
do use an index (indexed off of last_name for instance) come back in 1 or
2 seconds.

Am I doing something wrong?  The data is the same type so I don't need to 
cast, right?  What other tricks are there to get the query planner to
be smart?

Orion

Here's my exact table structure:

fdb=# \d mfps_orderinfo_435
Table "mfps_orderinfo_435"
  Attribute  |  Type   | Modifier
-+-+--
 order_number| integer | not null
 source_code | text|
 last_name   | text|
 first_name  | text|
 title   | text|
 address1| text|
 address2| text|
 city| text|
 state   | text|
 zip | text|
 telephone   | text|
 bill_method | text|
 cc  | text|
 exp | text|
 cc_auth_code| text|
 multi_billing_code  | text|
 order_header_status | text|
 order_date  | date|
 ship_date   | date|
 total_quantity  | integer |
 order_extension | money   |
 sales_tax   | money   |
 shipping| money   |
 total_discount  | money   |
 return_quantity | integer |
 return_amount   | money   |
 num_billings| integer |
 tracking_no1| text|
 tracking_no2| text|
 tracking_no3| text|
 email   | text|
 amount_paid | money   |
Indices: mfps_orderinfo_435_fname,
 mfps_orderinfo_435_lname,
 mfps_orderinfo_435_odate,
 mfps_orderinfo_435_pkey

fdb=# \d mfps_orderinfo_435_odate
Index "mfps_orderinfo_435_odate"
 Attribute  | Type
+--
 order_date | date
btree

fdb=# explain SELECT * FROM mfps_orderinfo_435 WHERE order_date = 
current_date;
NOTICE:  QUERY PLAN:

Seq Scan on mfps_orderinfo_435  (cost=0.00..14272.07 rows=1340 width=288)

EXPLAIN
fdb=# explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date >= 
to_date('2001-05-01','-MM-DD') AND order_date <= 
to_date('2001-10-10','-MM-DD');
NOTICE:  QUERY PLAN:

Aggregate  (cost=15115.73..15115.73 rows=1 width=4)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..15031.36 rows=33746 
width=4)

EXPLAIN
fdb=# explain verbose SELECT count(*) FROM mfps_orderinfo_435 WHERE 
order_date >= to_date('2001-05-01','-MM-DD') AND order_date <= 
to_date('2001-10-10','-MM-DD');
NOTICE:  QUERY DUMP:

{ AGG :startup_cost 15115.73 :total_cost 15115.73 :rows 1 :width 4 :state 
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 
:restypmod -1 :resname count :reskey 0 :reskeyop 0 :ressortgroupref 0 
:resjunk false } :expr { AGGREG :aggname count :basetype 0 :aggtype 23 
:target { CONST :consttype 23 :constlen 4 :constisnull false :constvalue  4 
[ 1 0 0 0 ]  :constbyval true } :usenulls false :aggstar true :aggdistinct 
false }}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 
15031.36 :rows 33746 :width 4 :state <> :qptargetlist <> :qpqual ({ EXPR 
:typeOid 16  :opType op :oper { OPER :opno 1098 :opid 1090 :opresulttype 16 
} :args ({ VAR :varno 1 :varattno 18 :vartype 1082 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 18} { EXPR :typeOid 1082  :opType 
func :oper { FUNC :funcid 1780 :functype 1082 :funcisindex false :funcsize 
0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 
:restype 1082 :restypmod -1 :resname \ :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno -1 :varattno 1 
:vartype 1082 :vartypmod -1  :varlevelsup 0 :varnoold -1 :varoattno 1}}) 
:func_planlist <>} :args ({ CONST :consttype 25 :constlen -1 :constisnull 
false :constvalue  14 [ 14 0 0 0 50 48 48 49 45 48 53 45 48 49 ]  
:constbyval false } { CONST :consttype 25 :constlen -1 :constisnull false 
:constvalue  14 [ 14 0 0 0 89 89 89 89 45 77 77 45 68 68 ]  :constbyval 
false })})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1096 :opid 
1088 :opresulttype 16 } :args (

[BUGS] tracking down a crash bug

2008-04-13 Thread Orion Henry

Hello.  I need help tracking down a crash bug.  I'm running 8.2.7
I've had my database go into recovery mode three times so far today under
user load.  /etc/init.d/postgresql-8.2 stop would stop the backend but leave
a few processes behind like this

postgres 22318  0.0  0.0  45724  1272 ?Ss   Apr11   0:13 postgres:
app1101 app1101 10.255.7.159(44567) idle

postgres 24365  0.0  0.0  45724  1224 ?Ss   Apr11   0:02 postgres:
app2280 app2280 10.255.7.159(51010) idle

postgres  5649  0.0  0.0  45368  1180 ?Ss   Apr11   0:00 postgres:
app9452 app9452 10.255.7.159(43141) idle

I would then have to kill -9 these process.  Looking at the postgres log I
find only this...

2008-04-13 12:20:10 PDT STATEMENT:  SELECT version FROM schema_info
2008-04-13 12:21:14 PDT ERROR:  relation "schema_info" does not exist
2008-04-13 12:21:14 PDT STATEMENT:  SELECT version FROM schema_info
2008-04-13 12:26:48 PDT LOG:  background writer process (PID 965) was
terminated by signal 9
2008-04-13 12:26:48 PDT LOG:  terminating any other active server processes
2008-04-13 12:26:48 PDT WARNING:  terminating connection because of crash of
another server process
2008-04-13 12:26:48 PDT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2008-04-13 12:26:48 PDT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
[ repeat several hundred times ]
2008-04-13 12:28:11 PDT FATAL:  the database system is in recovery mode
[ repeat several hundred times ]
2008-04-13 12:33:00 PDT LOG:  incomplete startup packet
2008-04-13 12:33:00 PDT LOG:  received fast shutdown request
2008-04-13 12:33:12 PDT FATAL:  the database system is shutting down
[ repeat a dozen times ]
2008-04-13 12:34:00 PDT LOG:  received immediate shutdown request
2008-04-13 12:34:02 PDT LOG:  could not load root certificate file
"root.crt": No such file or directory
2008-04-13 12:34:02 PDT DETAIL:  Will not verify client certificates.
2008-04-13 12:34:20 PDT LOG:  could not create IPv6 socket: Address family
not supported by protocol
2008-04-13 12:34:20 PDT LOG:  could not resolve "localhost": Name or service
not known
2008-04-13 12:34:20 PDT LOG:  disabling statistics collector for lack of
working socket
2008-04-13 12:34:20 PDT WARNING:  autovacuum not started because of
misconfiguration
2008-04-13 12:34:20 PDT HINT:  Enable options "stats_start_collector" and
"stats_row_level".
2008-04-13 12:34:20 PDT LOG:  database system was interrupted at 2008-04-13
12:22:44 PDT
2008-04-13 12:34:20 PDT LOG:  checkpoint record is at 0/594FDF58
2008-04-13 12:34:20 PDT LOG:  redo record is at 0/5946B830; undo record is
at 0/0; shutdown FALSE
2008-04-13 12:34:20 PDT LOG:  next transaction ID: 0/2979312; next OID:
106497
2008-04-13 12:34:20 PDT LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2008-04-13 12:34:20 PDT LOG:  database system was not properly shut down;
automatic recovery in progress
2008-04-13 12:34:20 PDT LOG:  redo starts at 0/5946B830
2008-04-13 12:34:21 PDT LOG:  incomplete startup packet
2008-04-13 12:34:21 PDT LOG:  record with zero length at 0/5957A3EC
2008-04-13 12:34:21 PDT LOG:  redo done at 0/5957A3C4
2008-04-13 12:34:21 PDT LOG:  database system is ready

Any advice on how I can get this bug identified and squashed?  I suspect
it's in the create/drop [database,role,schema].  I've used postgres for 7
years without issues at this point.  The only thing different now are my
usage patterns.  Since I'm offering a database as a service to my users I'm
adding and dropping databases roles and schemas constantly.

Thanks

[BUGS] tracking down a crash bug

2008-04-13 Thread Orion Henry

Hello.  I need help tracking down a crash bug.  I'm running 8.2.7
I've had my database go into recovery mode three times so far today under
user load.  /etc/init.d/postgresql-8.2 stop would stop the backend but leave
a few processes behind like this

postgres 22318  0.0  0.0  45724  1272 ?Ss   Apr11   0:13 postgres:
app1101 app1101 10.255.7.159(44567) idle
postgres 24365  0.0  0.0  45724  1224 ?Ss   Apr11   0:02 postgres:
app2280 app2280 10.255.7.159(51010) idle
postgres  5649  0.0  0.0  45368  1180 ?Ss   Apr11   0:00 postgres:
app9452 app9452 10.255.7.159(43141) idle

I would then have to kill -9 these process.  Looking at the postgres log I
find only this...

2008-04-13 12:20:10 PDT STATEMENT:  SELECT version FROM schema_info
2008-04-13 12:21:14 PDT ERROR:  relation "schema_info" does not exist
2008-04-13 12:21:14 PDT STATEMENT:  SELECT version FROM schema_info
2008-04-13 12:26:48 PDT LOG:  background writer process (PID 965) was
terminated by signal 9
2008-04-13 12:26:48 PDT LOG:  terminating any other active server processes
2008-04-13 12:26:48 PDT WARNING:  terminating connection because of crash of
another server process
2008-04-13 12:26:48 PDT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2008-04-13 12:26:48 PDT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
[ repeat several hundred times ]
2008-04-13 12:28:11 PDT FATAL:  the database system is in recovery mode
[ repeat several hundred times ]
2008-04-13 12:33:00 PDT LOG:  incomplete startup packet
2008-04-13 12:33:00 PDT LOG:  received fast shutdown request
2008-04-13 12:33:12 PDT FATAL:  the database system is shutting down
[ repeat a dozen times ]
2008-04-13 12:34:00 PDT LOG:  received immediate shutdown request
2008-04-13 12:34:02 PDT LOG:  could not load root certificate file
"root.crt": No such file or directory
2008-04-13 12:34:02 PDT DETAIL:  Will not verify client certificates.
2008-04-13 12:34:20 PDT LOG:  could not create IPv6 socket: Address family
not supported by protocol
2008-04-13 12:34:20 PDT LOG:  could not resolve "localhost": Name or service
not known
2008-04-13 12:34:20 PDT LOG:  disabling statistics collector for lack of
working socket
2008-04-13 12:34:20 PDT WARNING:  autovacuum not started because of
misconfiguration
2008-04-13 12:34:20 PDT HINT:  Enable options "stats_start_collector" and
"stats_row_level".
2008-04-13 12:34:20 PDT LOG:  database system was interrupted at 2008-04-13
12:22:44 PDT
2008-04-13 12:34:20 PDT LOG:  checkpoint record is at 0/594FDF58
2008-04-13 12:34:20 PDT LOG:  redo record is at 0/5946B830; undo record is
at 0/0; shutdown FALSE
2008-04-13 12:34:20 PDT LOG:  next transaction ID: 0/2979312; next OID:
106497
2008-04-13 12:34:20 PDT LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2008-04-13 12:34:20 PDT LOG:  database system was not properly shut down;
automatic recovery in progress
2008-04-13 12:34:20 PDT LOG:  redo starts at 0/5946B830
2008-04-13 12:34:21 PDT LOG:  incomplete startup packet
2008-04-13 12:34:21 PDT LOG:  record with zero length at 0/5957A3EC
2008-04-13 12:34:21 PDT LOG:  redo done at 0/5957A3C4
2008-04-13 12:34:21 PDT LOG:  database system is ready

Any advice on how I can get this bug identified and squashed?  I suspect
it's in the create/drop [database,role,schema].  I've used postgres for 7
years without issues at this point.  The only thing different now are my
usage patterns.  Since I'm offering a database as a service to my users I'm
adding and dropping databases roles and schemas constantly.

Thanks

[BUGS] segfault in psql on x86_64

2004-01-30 Thread Orion Henry





I am running vanilla Fedora Core 1 - test1 for x86_64
running an update system running kernel 2.4.22-1.2166.nptlsmp
The system is a dual opteron pogolinux box with 512M and a IDE RAID 1 from 3ware

I downloaded postgresql-7.4.1-1PGDG.src.rpm
and built it on the system. (rpmbuild --rebuild ...)
and installed the resulting RPM's.  

The database starts and stops normally on install

The psql tool ( as well as createuser/vacuum etc all seg fault)

It appears to be faulting on a kerberos call which is odd because I don't use kerberos for anything.

Here everything plus a gdb backtrace.

[EMAIL PROTECTED] data]# psql
Segmentation fault
[EMAIL PROTECTED] data]# gdb psql
GNU gdb Red Hat Linux (5.3.90-0.20030710.41rh)
Copyright 2003 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu"...Using host libthread_db library "/lib64/tls/libthread_db.so.1".

(gdb) run
Starting program: /usr/bin/psql
[Thread debugging using libthread_db enabled]
[New Thread 182894073344 (LWP 5363)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 182894073344 (LWP 5363)]
0x003de59010e0 in ?? () from /lib64/tls/libm.so.6
(gdb) bt
#0  0x003de59010e0 in ?? () from /lib64/tls/libm.so.6
#1  0x003de6e16c61 in krb5int_initialize_library () from /usr/lib64/libkrb5.so.3
#2  0x003de6e413fb in krb5_init_secure_context () from /usr/lib64/libkrb5.so.3
#3  0x003e2d40844e in pg_krb5_init (PQerrormsg=0x7fbfffda50 " �\177") at fe-auth.c:291
#4  0x003e2d4085a9 in pg_krb5_authname (PQerrormsg=0x3de6f6bc80 "4�") at fe-auth.c:348
#5  0x003e2d408ce1 in fe_getauthname (PQerrormsg=0x7fbfffda50 " �\177") at fe-auth.c:731
#6  0x003e2d40af56 in conninfo_parse (conninfo=0x5320c0 "uA->", errorMessage=0x529db0) at fe-connect.c:2720
#7  0x003e2d408db8 in connectOptions1 (conn=0x529a20, conninfo=0x3de6f6bc80 "4�") at fe-connect.c:328
#8  0x003e2d4091e3 in PQsetdbLogin (pghost=0x0, pgport=0x0, pgoptions=0x0, pgtty=0x0, dbName=0x0, login=0x0, pwd=0x0)
    at fe-connect.c:541
#9  0x0040a1db in main (argc=1, argv=0x7fbfffe058) at startup.c:183
(gdb)





-- 
Orion Henry <[EMAIL PROTECTED]>








signature.asc
Description: This is a digitally signed message part

[BUGS] Cant get planner to use index (7.1.3-1PGDG)

[BUGS] Index Scans Oddness

[BUGS] tracking down a crash bug

[BUGS] tracking down a crash bug

[BUGS] segfault in psql on x86_64

5 matches

Site Navigation

Mail list logo

Footer information