Better performance in Lucene Or SQL

I would say any database will handle at least a million rows gracefully if proper indexing is done, there is no reason for you to get into Lucene or Solr which will introduce you to new tasks like synchronization of your indexes with most current state of the DB.

Also, Lucene or Solr are very great for free text searching. This means if you search for "Bob Marley" on your Lucene "documents" then you will get all the document which has "Bob Marley", "Marley Bob" or only "Bob" and only "Marley" or even "Bob...lot of text...Marley". So using Lucene also depends on what kind of use cases you are trying to cover.

From the query you have shown I feel you will get good performance if you index metadatavalue.text_value metadatafieldregistry.metadata_schema_id and metadatafieldregistry.element columns. Also try converting your query to a join rather then an in query.

Thanks

With a prefix query on such a small dataset, both Solr and PostgreSQL should perform very well provided that the required columns are properly indexed.

You don't mention the schema of metadatafieldregistry table (in fact you say you have just one table, but your query uses two)

Look at the explain analyze output to see what the query plan is, and what is taking up the time to scan. Your subquery is correlated, which almost definitely isn't a good plan: in general, the schema smells of EAV. You may find a partial index helpful, to produce an index containing only those text values that you want to do a prefix search on (probably restricting metadata_schema_id and element)