[SQL] Question regarding indices
Hello List, I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? I can imagine that just querying a bunch of bigint would not make a big difference but what about several thousand of values? Would sorting them and sending the SQL query with ordered data influence the speed of the query? // Steve -- GRATIS: Spider-Man 1-3 sowie 300 weitere Videos! Jetzt freischalten! http://portal.gmx.net/de/go/maxdome -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question about PQexecParams
Hello list,
I would like to call a function from my C application by using libpq and
PQexecParams. My problem is that I don't know how to specify that I want to
send an array to the function.
Assume the function is called lookup_data and takes the following parameters:
lookup_data(integer,integer,bigint[])
I would like to specify the OID with my query. How would I do that? Assume I
would like to query 3 values for bigint:
const char *paramValues[5];
Oid paramTypes[5];
int paramLengths[5];
int paramFormats[5];
int32_t ivalue1 = htonl(value1);
paramValues[0] = (char *)ivalue1;
paramTypes[0] = INT4OID;
paramLengths[0] = sizeof(ivalue1);
paramFormats[0] = 1;
int32_t ivalue2 = htonl(value2);
paramValues[1] = (char *)ivalue2;
paramTypes[1] = INT4OID;
paramLengths[1] = sizeof(ivalue2);
paramFormats[1] = 1;
etc...
How would I tell libpq that the next 3 values are an array of bigint?
I tried to use INT8OID and specify the query like below but that did not work:
SELECT * FROM lookup_data($1,$2,{$3,$4,$5})
Probably I have to set the query to be:
SELECT * FROM lookup_data($1,$2,{$3})
Or:
SELECT * FROM lookup_data($1,$2,$3)
But what would I set for paramTypes? How can I say that the values are an array
of bigint? I assume that I can set paramValues to be an array and paramLengths
to be sizeof one value multiplied by the amount of elements in the array.
I am somehow lost and don't know how to call the function and pass an array to
libpq.
Can any one help me with this?
// Steve
--
GMX DSL SOMMER-SPECIAL: Surf Phone Flat 16.000 für nur 19,99 Euro/mtl.!*
http://portal.gmx.net/de/go/dsl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Steve stev...@gmx.net writes: I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? I can imagine that just querying a bunch of bigint would not make a big difference but what about several thousand of values? Would sorting them and sending the SQL query with ordered data influence the speed of the query? It's unlikely to make enough difference to be worth the trouble. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Original-Nachricht Datum: Sat, 11 Sep 2010 10:05:18 -0400 Von: Michael Gould mgo...@intermodalsoftwaresolutions.net An: Steve stev...@gmx.net Betreff: Re: [SQL] Question regarding indices Steve, Hello Michael, If I remember correctly the sort only works on the final result set and so doing a sort before the select isn't going to help I think you got me wrong. It's not that I want the RESULT to be sorted. I only asked if sorting the values in the query would have an effect on the speed of the query. Or to ask it the other way around: Is an index in PostgreSQL sorted or not? If it is sorted and the PostgreSQL parser is looking for entries in the index then having the values pre-sorted would probably be beneficial. Right? // Steve Sent from Samsung mobile Steve stev...@gmx.net wrote: Hello List, I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? I can imagine that just querying a bunch of bigint would not make a big difference but what about several thousand of values? Would sorting them and sending the SQL query with ordered data influence the speed of the query? // Steve -- GRATIS: Spider-Man 1-3 sowie 300 weitere Videos! Jetzt freischalten! http://portal.gmx.net/de/go/maxdome -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- GRATIS: Spider-Man 1-3 sowie 300 weitere Videos! Jetzt freischalten! http://portal.gmx.net/de/go/maxdome -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Original-Nachricht Datum: Sat, 11 Sep 2010 11:04:16 -0400 Von: Tom Lane t...@sss.pgh.pa.us An: Steve stev...@gmx.net CC: pgsql-sql@postgresql.org Betreff: Re: [SQL] Question regarding indices Steve stev...@gmx.net writes: I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? I can imagine that just querying a bunch of bigint would not make a big difference but what about several thousand of values? Would sorting them and sending the SQL query with ordered data influence the speed of the query? It's unlikely to make enough difference to be worth the trouble. Making a quick sort is ultra easy in C. Anyway... is there a difference in the speed of the query with pre-sorted values or not? If there is one then I will go and sort the values. Right now I have a quick sort implemented but I will probably do a lazy quick sort and then a final insertion sort, because insertion is faster on a slightly ordered dataset than quick sort. Probably I am pulling hairs here about the speed but I really want to minimize the time it needs for PostgreSQL to return the data. I personally am happy with the speed when using PostgreSQL but the application I use has an MySQL driver too and I got some users claiming that MySQL is faster than PostgreSQL, transfers less data over the wire, etc... and I want to optimize the PostgreSQL part to be on the same level as the MySQL part. So everything that helps to squeeze the last nanosecond out of the PostgreSQL part is welcome. I already switched to binary transmission in order to minimize the data send over the wire when using PostgreSQL and I have added an function to do +/- what the MySQL proprietary INSERT ON DUPLICATE KEY UPDATE does. I hate when users compare apples with oranges but what can I do? You can not explain them that PostgreSQL is different and more standard compliant and that the chance to loose data is lower with PostgreSQL then with all that what MySQL is d oing (MyISAM tables, etc...). It's pointless to explain to them. It's like trying to explain a mole how the sun is shining. So all I want is to explore the available capabilities of PostgreSQL to get the best out of the libpq engine as possible. If you have any recommendation at what I should look in order to get better speed, then let me know. regards, tom lane // Steve -- Achtung Sicherheitswarnung: GMX warnt vor Phishing-Attacken! http://portal.gmx.net/de/go/sicherheitspaket -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding indices
Steve stev...@gmx.net writes: Von: Tom Lane t...@sss.pgh.pa.us It's unlikely to make enough difference to be worth the trouble. Making a quick sort is ultra easy in C. Anyway... is there a difference in the speed of the query with pre-sorted values or not? If there is one then I will go and sort the values. I didn't opine on whether it was easy or not. I said it was unlikely to be worth the trouble. You could very well spend more time sorting the values than you buy in whatever you might save on the server side. Each value in the IN list is going to require a separate index probe. The sorting might buy something in locality of reference for successive probes, but most likely not enough to notice. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about PQexecParams
Hey Steve,
2010/9/11 Steve stev...@gmx.net
Hello list,
I would like to call a function from my C application by using libpq and
PQexecParams. My problem is that I don't know how to specify that I want to
send an array to the function.
Assume the function is called lookup_data and takes the following
parameters: lookup_data(integer,integer,bigint[])
I would like to specify the OID with my query. How would I do that? Assume
I would like to query 3 values for bigint:
const char *paramValues[5];
Oid paramTypes[5];
int paramLengths[5];
int paramFormats[5];
int32_t ivalue1 = htonl(value1);
paramValues[0] = (char *)ivalue1;
paramTypes[0] = INT4OID;
paramLengths[0] = sizeof(ivalue1);
paramFormats[0] = 1;
int32_t ivalue2 = htonl(value2);
paramValues[1] = (char *)ivalue2;
paramTypes[1] = INT4OID;
paramLengths[1] = sizeof(ivalue2);
paramFormats[1] = 1;
etc...
How would I tell libpq that the next 3 values are an array of bigint?
I tried to use INT8OID and specify the query like below but that did not
work:
SELECT * FROM lookup_data($1,$2,{$3,$4,$5})
Incorrect.
Probably I have to set the query to be:
SELECT * FROM lookup_data($1,$2,{$3})
Incorrect.
Or:
SELECT * FROM lookup_data($1,$2,$3)
Correct. You may specify a data type by OID (1016 for bigint[],
please refer to
http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html
to obtain information about types) or attach an explicit cast to a
parameter symbol to force treating it as bigint[] (or any specified type),
e.g.
SELECT * FROM lookup_data($1, $2, $3::bigint[])
But what would I set for paramTypes? How can I say that the values are an
array of bigint? I assume that I can set paramValues to be an array and
paramLengths to be sizeof one value multiplied by the amount of elements in
the array.
Please note, that in this case, you must pass to paramValues[2] a textual
representation
of bigint[], e.g. '{1,2,3}'.
Its not necessary to specify a length of text-format parameters (its
ignored).
The length is essential only if you transmit data in a binary format.
I am somehow lost and don't know how to call the function and pass an array
to libpq.
Can any one help me with this?
Hope this helps.
Regards,
Dmitriy
[SQL] 9.0rc1 - query of view produces unexpected results
Hello list, I don't know if it's me, or maybe even the way I designed these dependencies, but I'm getting unexpected results when i query a specific view. I can only reproduce the unexpected results with a very specific set of tables, views, and relationships. The bad results are only manifest in 9.0 (beta and rc1). Version 8.4.4 produces the expected results. To sum up the issue ... 1. View register_orders_view contains only 1 row of data ... /ttype | ropnum | shop_name ---++--- or| 30129 | District1/ 2. The query ... / select * from register_orders_view where ttype='z'/ ... should return no rows, however all rows are returned. Included are a buildDemo.sql file that will create the necessary tables and views, and a runDemo.sql to demonstrate the query from #2 above. If this is a valid result, can someone please help me understand why? If this is a case of not the best architecture a explanation would be greatly apprecitated. Or if this is a bug with 9.0, what actions do I take to make the PostgreSQL team awares? nate
